Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
487 votes
2 answers
259k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
Martin Smith's user avatar
  • 85.4k
300 votes
4 answers
352k views

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log ...
Mike Walsh's user avatar
  • 18.2k
280 votes
7 answers
155k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
bernd_k's user avatar
  • 12.3k
243 votes
18 answers
122k views

How do you document your databases?

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using. ...
208 votes
6 answers
162k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed ...
Stuart Blackler's user avatar
191 votes
7 answers
216k views

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, ...
Rachel's user avatar
  • 8,487
177 votes
20 answers
62k views

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
Tom Hunter's user avatar
  • 2,169
175 votes
6 answers
177k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
BrunoLM's user avatar
  • 3,513
172 votes
4 answers
178k views

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

Any suggestions on how to deal with this error: TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit ...
Jonathan Allen's user avatar
158 votes
12 answers
442k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
user avatar
130 votes
7 answers
155k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
misterjaytee's user avatar
  • 1,403
127 votes
5 answers
327k views

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ...
Michael Richardson's user avatar
120 votes
3 answers
89k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
elranu's user avatar
  • 1,303
119 votes
4 answers
22k views

Why are numbers tables "invaluable"?

Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table: USE Model GO CREATE TABLE Numbers ( Number INT NOT NULL, ...
Jeff Atwood's user avatar
  • 2,364
113 votes
6 answers
103k views

SQL Server Management Studio 18 won't open (only splash screen pops up)

I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits. Running ssms with the -log parameter ...
Mitch's user avatar
  • 2,638
108 votes
2 answers
261k views

Connect to SQL Server with Windows Authentication in a different domain

I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed ...
stergosz's user avatar
  • 1,205
107 votes
7 answers
45k views

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost ...
user606723's user avatar
  • 1,526
105 votes
6 answers
79k views

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
Paul White's user avatar
  • 87.1k
103 votes
18 answers
43k views

Is adding the ‘tbl’ prefix to table names really a problem?

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation,...
Racer SQL's user avatar
  • 7,416
103 votes
8 answers
21k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
Curtis's user avatar
  • 1,255
99 votes
4 answers
397k views

SQL server databases stuck in restoring state

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state! Is it possible to stop the restoring process? and also, How can I make sure ...
Pooya Yazdani's user avatar
98 votes
5 answers
65k views

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's ...
Richard's user avatar
97 votes
8 answers
307k views

How to query last restore date in SQL Server?

Is there a T-SQL query that shows the last restore datetime for a certain database?
Kees C. Bakker's user avatar
96 votes
4 answers
21k views

Authoritative source that <> and != are identical in performance in SQL Server

Consider this answer on SO that reassures the asker about the <> operator that: <> is ... the same as !=. But then a commenter pipes up and says: It's true that they are, functionally, ...
ErikE's user avatar
  • 4,315
95 votes
6 answers
262k views

SQL Server Cannot drop database <dbname> because it is currently in use... but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. ...
tuseau's user avatar
  • 1,885
94 votes
6 answers
95k views

Why would SET ARITHABORT ON dramatically speed up a query?

The query is a single select containing a lot of grouping levels and aggragate operations. With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this ...
Jonathan Allen's user avatar
92 votes
2 answers
292k views

How to get the current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
Piers Myers's user avatar
  • 1,033
87 votes
4 answers
50k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
Erik Darling's user avatar
  • 42.1k
85 votes
5 answers
48k views

Which database could handle storage of billions/trillions of records?

We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
somecallmemike's user avatar
85 votes
2 answers
178k views

How to add the Debug button to SSMS v18?

The Debug button is present on this version of SSMS: But it is not present on version 18, preview 4: I have tried in several ways to add the Debug button to my SSMS but not successful. Is there a ...
Marcello Miorelli's user avatar
83 votes
4 answers
55k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
David Clarke's user avatar
  • 1,197
83 votes
1 answer
188k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
srk's user avatar
  • 931
81 votes
2 answers
176k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
Shaul Behr's user avatar
  • 2,933
81 votes
5 answers
29k views

Are SQL Server in-place upgrades as ill advised as they used to be?

I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade. I'm currently upgrading my 2008 R2 DEV and TEST ...
DamagedGoods's user avatar
  • 2,591
81 votes
4 answers
214k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
prasanth's user avatar
  • 1,033
80 votes
5 answers
82k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
Adam Butler's user avatar
  • 1,531
79 votes
1 answer
100k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It is ...
KutuluMike's user avatar
  • 1,589
79 votes
7 answers
78k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
Nick Chammas's user avatar
  • 14.7k
78 votes
3 answers
68k views

What is the difference between a connection and a session?

What is the difference between a connection and a session and how they are related?
jrara's user avatar
  • 5,353
75 votes
8 answers
70k views

MAXDOP setting algorithm for SQL Server

When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's NUMA ...
Hannah Vernon's user avatar
  • 70.2k
71 votes
7 answers
100k views

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (SQL Server)? Is there a case for rebuilding indexes on a regular basis?
Nick Chammas's user avatar
  • 14.7k
70 votes
4 answers
59k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
Greg's user avatar
  • 3,272
69 votes
12 answers
321k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
D. Lambert's user avatar
  • 1,057
66 votes
8 answers
47k views

What is the difference between a "record" and a "row" in SQL Server?

There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate. So how do we differentiate between these related terms and how we ...
swasheck's user avatar
  • 10.7k
65 votes
4 answers
84k views

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the ...
Jonesome Reinstate Monica's user avatar
65 votes
1 answer
13k views

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure ...
Paul White's user avatar
  • 87.1k
65 votes
4 answers
144k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
Juan Velez's user avatar
  • 3,253
64 votes
5 answers
253k views

Write differences between varchar and nvarchar

Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that. My question is are there any differences in how SQL Server ...
Chris L's user avatar
  • 931
64 votes
1 answer
23k views

What event information can I get by default from SQL Server?

I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its ...
Aaron Bertrand's user avatar
63 votes
6 answers
70k views

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
Paul White's user avatar
  • 87.1k

1
2 3 4 5
689