This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

11
votes
1answer
846 views

Workspace Memory Internals

Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation ...
11
votes
3answers
4k views

Maximum table capacity in SQL Server 2008

I have an application which inserts more than 1 billion rows annually into a table. This table contains some varchar and bigint columns and one blob column as well. The 1 billion rows consist of ...
11
votes
2answers
3k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
11
votes
1answer
2k views

What is the impact of changing the index creation memory setting in SQL Server?

I am trying to create some indexes a SQL Server 2008 database but am getting the following error message. Msg 8606, Level 17, State 1, Line 1 This index operation requires 1024 KB of memory ...
11
votes
3answers
2k views

Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

SQL server 2008 R2 query optimizer puzzle We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same. This is fast, 13 seconds, select * from bigtable1 ...
11
votes
0answers
280 views

SQL Server Analysis Studio naive Bayes attribute has too many states [closed]

I'm using SQL Server Analysis Studio and I'm trying to train a naive Bayes classifier. I've got an attribute called "item description" that basically represents products you can buy at a store. I've ...
10
votes
5answers
4k views

Do empty columns take up space in a table?

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter ...
10
votes
4answers
2k views

How can I write windowing query which sums a column to create discrete buckets?

I have a table which includes a column of decimal values, such as this: id value size -- ----- ---- 1 100 .02 2 99 .38 3 98 .13 4 97 .35 5 96 .15 6 95 .57 7 94 .25 ...
10
votes
5answers
15k views

Save Results as CSV *with* headers in SSMS

With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in ...
10
votes
6answers
2k views

SQL Server command line usage? Is it possible?

I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's ...
10
votes
3answers
1k views

SQL Server 2008/R2 recovery model

Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the ...
10
votes
1answer
745 views

Why does 10^37 / 1 throw an arithmetic overflow error?

Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code: DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37); PRINT ...
10
votes
1answer
4k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
10
votes
3answers
2k views

SQL Profiler tutorials for newbie

The MSDN documentation tough to understand. Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.
10
votes
2answers
428 views

@table_variable or #temp_table

I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
10
votes
1answer
1k views

If a transaction is “committed” , is it then saved for sure?

If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ? Today our log files had ...
10
votes
2answers
8k views

In Sql Server's RANK function, how to limit the results to just the first 3 ranks

In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
10
votes
1answer
3k views

Empty Strings: Why or when is '' equal to ' '?

Who can explain why select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' '); yields ----------- ----------- ----------- ----------- ----------- 1 ...
10
votes
3answers
3k views

using *tables* as Table-Valued Parameters (TVP)

MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing. Rather than create a user-defined type, is it possible to leverage an existing table ...
10
votes
2answers
2k 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
776 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
5answers
2k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
10
votes
3answers
2k 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
1answer
5k views

How to use transactions with SQL Server DDL?

I have a login table into which all inserts are done by a single stored procedure. CREATE TABLE dbo.LogTable( LogRefnr int IDENTITY(1, 1) NOT NULL, LogQuery varchar(255) NOT NULL, LogTime ...
10
votes
2answers
3k 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
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
3k 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
2k 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
2k 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
4k 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
5answers
17k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
10
votes
1answer
818 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
1answer
2k views

Does SQL Server 2008 store the creation date of execution plans?

We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
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
1answer
5k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
10
votes
1answer
774 views

Slot Array and Total Page Size

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B This leaves ...
10
votes
1answer
883 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
1answer
713 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
6k 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
3answers
2k 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
5answers
27k views

Restoring a backup to an older version of SQL Server

When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) ...
9
votes
3answers
42k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
9
votes
3answers
6k 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
31k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
9
votes
2answers
832 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
11k 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
5answers
28k views

How to query last restore date in SQL Server?

Is there a TSQL query that shows the last restore datetime for a certain database?
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 ...