Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

11
votes
3answers
12k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
7
votes
4answers
60k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
5
votes
3answers
1k views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
29
votes
5answers
5k 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 ...
18
votes
3answers
2k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
14
votes
9answers
2k views

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
14
votes
3answers
7k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
13
votes
1answer
6k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
13
votes
2answers
7k views

Broken NTFS Permissions for MSSQL$SQLEXPRESS

I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created. D:\SQL D:\SQL\Data D:\SQL\Backup etc.. I kept the ...
13
votes
3answers
2k 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 ...
12
votes
3answers
6k views

Is there a way to determine if SQL Server queries are running in memory or going to disk?

I came across a set of stored procedures in an application today that are called repeatedly within a long running process. Within each procedure I found multiple different select statements, some ...
12
votes
4answers
771 views

Inaccurate 'Actual' row counts in parallel plan

This is a purely academic question, in so much that it isn't causing a problem and I'm just interested to hear any explanations for the behaviour. Take a standard issue Itzik Ben-Gan cross-join CTE ...
10
votes
5answers
8k views

SQL Server equivalent to functionality of Oracle RAC? [closed]

I did some Googling and couldn't find an answer to this question more recent than a few years ago, so I thought I'd ask. Oracle's RAC feature offers load-balancing for both read and write ...
10
votes
1answer
692 views

Definitive list of steps for SQL Server baseline testing?

Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to ...
8
votes
2answers
7k views

How badly do SQL Compilations impact the performance of SQL Server?

I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so. I whipped out SQL Profiler ...
8
votes
3answers
15k views

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
7
votes
2answers
2k views

Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks. So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities. ...
5
votes
3answers
20k views

T-SQL query to report disk space allocation and used space for auto-grow SAN databases

We are using 6 databases in total for an application, and we can only share 4TB of space for all 6 databases (via SAN storage). I'd like to write a query (report) for a single database indicating the ...
5
votes
1answer
16k views

SQL Server permissions to create, restore, delete only some databases

On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security ...
5
votes
2answers
4k views

Most Efficient Way to Retrieve Date Ranges

What's the most efficient way to retrieve date ranges with a table structure like this? create table SomeDateTable ( id int identity(1, 1) not null, StartDate datetime not null, EndDate ...
4
votes
2answers
4k views

Find orphaned users

In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) ...
3
votes
2answers
235 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...
26
votes
7answers
8k views

Why should I create an ID column when I can use others as key fields? [duplicate]

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not ...
19
votes
5answers
9k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
19
votes
3answers
37k views

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In ...
16
votes
3answers
22k views

How do I run a large script with many inserts without running out of memory?

Question: I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run? ...
16
votes
1answer
2k views

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in ...
15
votes
2answers
4k views

Restore database excluding FILESTREAM data

Context We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB. Out of these, ...
15
votes
3answers
3k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
11
votes
1answer
4k views

SQL Server Cache Flush and Disk I/O

We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
11
votes
2answers
992 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
10
votes
1answer
2k views

What's a good SQL Server backup schedule?

There seems to be a plethora of information regarding the process of setting up backup tasks, but not much information about the big-picture view of database backups. At least, it's hard to formulate ...
9
votes
2answers
6k views

SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available)

I have SQL Server 2012 Local DB Installed, and am connecting to it via (localdb)\v11.0. When trying to restore a backup file, I get: TITLE: Microsoft SQL Server Management Studio Property ...
9
votes
1answer
951 views

alert when any job in a job category fails

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails? I am wondering because I would like to set up an e-mail anytime an SSRS ...
9
votes
1answer
2k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
8
votes
3answers
10k views

Improve performance of sys.dm_db_index_physical_stats

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on ...
7
votes
3answers
25k views

How do I run a batch file from a scheduled Job via SQL Server Agent

I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I ...
6
votes
2answers
2k views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
6
votes
3answers
3k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
5
votes
3answers
428 views

Get MS SQL Server to release disk space

A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx ...
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
22
votes
6answers
17k views

SQL Server load balancing

What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased? I'm trying to ...
16
votes
2answers
48k views

Sql Server database in recovery: How to stop, Take ofline or delete?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I dont care one ...
15
votes
6answers
43k views

How to efficiently check EXISTS on multiple columns?

This is an issue I come up against periodically and have not yet found a good solution for. Supposing the following table structure CREATE TABLE T ( A INT PRIMARY KEY, B CHAR(1000) NULL, C ...
14
votes
5answers
9k views

Does restarting SQL Server speed it up?

I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart ...
13
votes
1answer
1k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
12
votes
2answers
773 views

Understanding MS SQL Server encryption and backups

So I was asking in chat about how secure SQL Server backup files are. I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data. So lets look at ...
12
votes
2answers
3k views

Storing massive amounts of data from a sensor array

I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to ...
11
votes
1answer
2k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
11
votes
5answers
3k views

Does it make sense to use SQL Server's bracket notation in hand written code?

Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything. When I first saw it, I though wow a reincarnation of the somewhat ...