Tagged Questions
29
votes
4answers
2k views
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.
I added a a NULLABLE column with ...
26
votes
4answers
8k views
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
15
votes
3answers
2k views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
14
votes
3answers
1k views
Painless way to create a clustered index on a huge table?
So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
14
votes
3answers
7k views
Execution Plan Basics — Hash Match Confusion
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:
select Posts.Title, Users.DisplayName
From Posts JOIN Users on
...
14
votes
1answer
3k views
SLEEP_TASK Wait Type in SQL Server - What does it indicate?
I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them.
I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
13
votes
2answers
329 views
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
12
votes
3answers
1k views
Can you explain this execution plan?
I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
12
votes
3answers
922 views
Data obfuscation in SQL Server
What is the best practice for Data Obfuscation in SQL Server?
We'd like to use masked Production data in our UAT system.
If we want to do it quickly, and with a higher level of obsfucation, what ...
12
votes
3answers
818 views
Slow deletion of records when a trigger is enabled
Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve.
http://support.microsoft.com/kb/2606883
Ok so I have an ...
11
votes
3answers
810 views
How often to backup the master database?
The suggestion in BOL is fairly vague:
Back up master as often as necessary to protect the data sufficiently
for your business needs. We recommend a regular backup schedule, which
you can ...
11
votes
2answers
323 views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
11
votes
3answers
660 views
Is there any benefit to defragmenting SQL indexes in a SAN environment?
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.
We have been running Ola Hallengren's index maintenance scripts weekly, and ...
11
votes
3answers
802 views
Does the partition key also have to be part of the primary key?
I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
