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)

19
votes
3answers
7k views

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
21
votes
7answers
34k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
12
votes
5answers
19k views

SQL Server 2012 slower than 2008

I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
12
votes
1answer
6k views

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. ...
24
votes
1answer
5k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
21
votes
2answers
2k views

Why does query error with empty result set in SQL Server 2012?

When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
17
votes
1answer
9k views

Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing

We are using SQL authentication & .net 4.0 Connection strings to connect to an Enterprise Edition 2012 SP1 SQL Server on a windows 2008r2 Enterprise Server. We use about 50 Servers split into 8 ...
16
votes
2answers
1k views

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
8
votes
2answers
4k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
6
votes
4answers
6k views

SQL Server - Separating data, log, and TempDB files on a SAN

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
32
votes
3answers
2k views

Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
8
votes
2answers
3k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
7
votes
2answers
12k views

After moving database (backup, restore), I have to re-add user

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio. Whenever I do this, the ...
6
votes
4answers
37k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
6
votes
3answers
2k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
6
votes
6answers
3k views

Recommendations for MS SQL Server monitoring software or service? [closed]

I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database ...
5
votes
2answers
4k views

Splitting TempDB into multiples files equal to number of CPUs

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files. By ...
20
votes
3answers
6k 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 ...
16
votes
2answers
2k views

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table ...
12
votes
4answers
3k views

What tools are there to generate test data for SQL Server?

As you can see from another question of mine, generating test data is my theme right now. At this point, I'm still generating my test data by hand. However, this process always generates small ...
10
votes
2answers
4k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
8
votes
3answers
775 views

sql server database sharding - what to do with common data / non sharded data

We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very ...
8
votes
5answers
1k views

Inclusion of ORDER BY on query that returns no rows drastically affects performance

Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
7
votes
2answers
2k views

Is there a better option than Union All for multiple selects from the same row?

Example Schema: CREATE TABLE [dbo].[Base]( [ID] [int] IDENTITY(1,1) NOT NULL, [Option1ID] [int] NULL, [Option2ID] [int] NULL, [Option3ID] [int] NULL, [Option1Name] [varchar] NULL, [Option2Name] ...
3
votes
1answer
1k views

In MS SQL how do I find out what tables have not been queried or updated in a long time?

I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to ...
18
votes
4answers
2k views

What are the consequences of setting varchar(8000)?

Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server? On create table, if ...
14
votes
3answers
9k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
12
votes
4answers
3k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
12
votes
3answers
3k views

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
11
votes
2answers
12k views

How To Profile Stored Procedures

I am using SQL Server 2012 and was wondering how to profile stored procedures For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes ...
11
votes
1answer
727 views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
10
votes
6answers
5k views

Suddenly Slow Execution Plan for Stored Proc

I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
9
votes
4answers
9k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
8
votes
1answer
9k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
8
votes
3answers
244 views

Nonclustered Index Insert

Say I have a table like this: create table SomeTable ( id int identity(1, 1) not null primary key clustered, SomeString1 varchar(50) not null, SomeString2 varchar(50) not null ) go ...
8
votes
2answers
9k views

Difference Between Row Level and Page Level Locking and Consequences

When attempting to run my Maintenance Plan, I receive the following error: Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized ...
7
votes
2answers
3k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
7
votes
5answers
7k 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 ...
7
votes
1answer
3k views

SQL Server Deadlock Graph - Table, Page or Row Lock?

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
6
votes
2answers
21k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
6
votes
1answer
16k views

Finding last time table was updated?

The query: SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate will tell me the last time a table was created and modified (from a ...
6
votes
4answers
20k views

SQL performance issues with remote query across linked server

This sproc create proc dbo.Get_Accounts as begin declare @current_date datetime set @current_date = dbo.fn_currdate() select [fields] into dbo.current_accounts from ...
6
votes
1answer
3k views

Whats the difference between Transaction logs and Binary logs?

Coming from a SQL Server shop, I now work with MySQL and I was curious. What are the differences between MySQL's binary log and MSSQL's transaction log? By the outlook thus far, it seems that there ...
5
votes
1answer
1k views

Allow non-sys admin, non-owner of a job to execute it

I have a job that runs an SSIS package. Currently the owner is a proxy account. I can run the job manually from a sys-admin account. Our web service logs in using a limited account. It needs to ...
5
votes
3answers
181 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
5
votes
2answers
2k views

SQL Server - Tempdb data/log file placement

Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive. On monitoring we have found I/O issues on TempDB. We got an extra drive available - should we move the TempDB Log ...
4
votes
3answers
564 views

Why does my procedure run slowly until I update statistics?

I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
4
votes
2answers
3k views

Why is SQL Server memory showing in task manager

I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that ...
0
votes
2answers
157 views

Determine whether there is any blocking while deleting large number of records [closed]

I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure. I have clustered index on primary ...
0
votes
2answers
1k views

help in multi-language medical database?

My project is to design a medical database for many countries. This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language. I started my ...