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.
33,451
questions
482
votes
2
answers
254k
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?
- 83k
297
votes
4
answers
344k
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 ...
- 18.1k
271
votes
7
answers
144k
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 ...
- 12.1k
243
votes
18
answers
120k
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.
...
206
votes
6
answers
155k
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 ...
- 4,512
188
votes
7
answers
212k
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, ...
- 8,417
174
votes
19
answers
60k
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 ...
- 2,139
172
votes
4
answers
177k
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 ...
- 3,542
170
votes
6
answers
161k
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 ...
- 3,393
156
votes
12
answers
434k
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?
user2645263
129
votes
7
answers
148k
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 ...
- 1,393
119
votes
4
answers
21k
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,
...
- 2,364
119
votes
3
answers
86k
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 ...
- 1,293
118
votes
5
answers
292k
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
...
- 1,355
113
votes
6
answers
99k
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 ...
- 2,628
107
votes
7
answers
44k
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 ...
- 1,516
106
votes
2
answers
253k
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 ...
- 1,185
104
votes
18
answers
41k
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,...
- 7,344
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:
...
- 1,255
103
votes
6
answers
77k
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 ...
- 82.1k
99
votes
4
answers
395k
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 ...
- 1,103
97
votes
5
answers
64k
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 ...
- 6,333
95
votes
4
answers
20k
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, ...
- 4,295
95
votes
8
answers
291k
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?
- 1,233
94
votes
6
answers
252k
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. ...
- 1,875
92
votes
6
answers
90k
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 ...
- 3,542
91
votes
2
answers
290k
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
- 1,023
86
votes
4
answers
47k
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 ...
- 38.2k
85
votes
5
answers
47k
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 ...
- 965
82
votes
1
answer
183k
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 ...
- 921
82
votes
2
answers
161k
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 ...
- 15.7k
81
votes
4
answers
53k
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 ...
- 1,167
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 ...
- 2,591
81
votes
4
answers
207k
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.
- 1,033
80
votes
5
answers
81k
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 ...
- 1,531
79
votes
2
answers
163k
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 ...
- 2,913
78
votes
1
answer
95k
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 ...
- 1,579
78
votes
7
answers
76k
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 ...
- 14.6k
77
votes
3
answers
63k
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?
- 5,323
75
votes
8
answers
67k
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 ...
- 69.5k
70
votes
4
answers
58k
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 ...
- 3,222
69
votes
12
answers
313k
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 ...
- 1,057
68
votes
7
answers
97k
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?
- 14.6k
66
votes
8
answers
44k
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 ...
- 10.6k
65
votes
5
answers
252k
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 ...
- 933
64
votes
4
answers
135k
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 ...
- 3,235
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 ...
- 179k
63
votes
6
answers
68k
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,
...
- 82.1k
62
votes
6
answers
39k
views
What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?
What this isn't about:
This is not a question about catch-all queries that accept user input or use variables.
This is strictly about queries where ISNULL() is used in the WHERE clause to replace ...
- 38.2k
61
votes
4
answers
73k
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 ...
