SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

10
votes
2answers
6k views

SQL Server 2008 - Cross database performance on same physical machine and server instance

Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance. For ...
10
votes
1answer
2k views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
10
votes
2answers
943 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
10
votes
2answers
3k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
10
votes
3answers
883 views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], ...
10
votes
2answers
14k views

Suppress SQL Server Management Studio auto complete

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a ...
10
votes
2answers
5k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
10
votes
3answers
3k views

Snapshot Replication Retention

I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder. How can I change the ...
10
votes
2answers
11k views

How do I switch off SCHEMABINDING for a view without recreating it?

How do I switch off SCHEMABINDING for a view without recreating it?
10
votes
1answer
210 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
10
votes
2answers
17k views

Maximum memory setting in SQL Server

I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available. As is noted elsewhere, SQL Server regularly takes up to 98% of physical ...
10
votes
2answers
3k views

Do SQL Server Express Editions support CLR procedures?

Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling and running CLR Stored procedures and functions.
10
votes
1answer
4k views

How to find corrupt page in SQL Server 2008

I know I can execute a DBCC CHECKDB and get a status of my database. Questions How can I find if there is some corrupt data page into my database? If an error is throwndue to page corruption, where ...
10
votes
2answers
3k views

How large is “too large” for SQL 2008 Web Edition?

For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently ...
10
votes
2answers
3k views

Is data retrieved from SQL Server compressed for transmission?

Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it? I'm examining analysis ...
10
votes
1answer
9k views

Mirroring - Server network address cannot be reached

I have SQL Server 2008 R2 Installed. It contains three instances. Default (MSSQLServer) First Instance Second Instance All these are Log On as Network Service.. Default Instance is Principal ...
10
votes
1answer
843 views

Why is “Lookup” colored as a function reserved word in SQL Server?

In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why? I cannot find it in the official list of reserved words. Searches ...
10
votes
2answers
4k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
10
votes
1answer
5k views

Compact Large Objects in Reorganize Index task

I'm setting up a maintenance plan in SQL Server 2008 R2. On the Reorganize Index Task it has an option for "Compact Large Objects". What does that mean and do I want to turn it on?
10
votes
1answer
5k views

Is it possible to create an alias or synonym for a database?

Is it possible to create a database alias or synonym? What I am hoping to be able to do is have NewDatabaseName aliased as OldDatabaseName to prevent having to rewrite a ton of code. Backstory: I ...
10
votes
1answer
943 views

Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
10
votes
3answers
3k views

Database starts always in recovery mode

Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal. This always and only happens when I restart the server, so I have a ...
10
votes
2answers
481 views

Is there a set based way to load/read a tree branch with HierarchyId

I am playing around with HierarchyId, and I have not figured out a set based way to do the following: insert all subtree at once retrieve all subtree at once This question is related to my ...
10
votes
2answers
1k views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
10
votes
2answers
485 views

What's the best method to refresh only a few tables within a test database from production?

I have a very large production database and a very large test environment database in SQL Server 2008R2. Both databases have similar table structure but different users/logins/permissions/roles. I ...
10
votes
3answers
2k views

Query is slow for certain users

I have a couple queries called from a C# .NET web application which are always fast for me (I am an local admin on the SQL Server) but for a group of users (domain group with required permissions), ...
10
votes
1answer
10k views

BULK INSERT permission on a single database

I work on a company that has straight IT security manners. I was hired as a Database Analyst in order to optimize some processes in our team. The problem is that I only have read and write permissions ...
10
votes
1answer
922 views

SQL Server 2008 R2 Dirty reads - how non-atomic?

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but: Can a row appear as ...
10
votes
3answers
5k views

TSQL performance - JOIN on value BETWEEN min and max

I have two tables in which I store: an IP range - country lookup table a list of requests coming from different IPs The IPs were stored as bigints to improve lookup performance. This is the table ...
10
votes
1answer
804 views

query optimization: time intervals

In the main, I've got two kinds of time intervals: presence time and absence time absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
10
votes
1answer
1k views

Scalar function used in calculated column, what's the cleanest way to update the function?

I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the ...
9
votes
6answers
10k views

Estimate projected database growth

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 ...
9
votes
6answers
3k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
9
votes
5answers
11k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
9
votes
2answers
55k views

I can't connect to my servers SQL database via an IP Address

I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed. I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS. However ...
9
votes
3answers
3k views

How to change the firing order of Triggers?

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
9
votes
4answers
2k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
9
votes
3answers
514 views

How can I drop all triggers in a single database?

I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?
9
votes
3answers
8k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
9
votes
3answers
754 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
9
votes
2answers
969 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
9
votes
3answers
14k views

How to see what is cached in memory in SQL server 2008?

Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: ...
9
votes
1answer
2k views

Why does sql server need to convert count(*) result into int before comparing it with an int variable?

I have many queries in my application where in the having clause, I have comparison of count aggregate function with int variable. In the query plans, I can see an implicit_convert before the ...
9
votes
3answers
6k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
9
votes
2answers
264 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
9
votes
7answers
957 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
9
votes
2answers
4k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
9
votes
1answer
2k views

SQL Server 2000 to 2008 to 2012

We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
9
votes
5answers
7k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
9
votes
4answers
620 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...