Tagged Questions
115
votes
3answers
18k 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?
44
votes
4answers
4k 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 ...
41
votes
7answers
1k 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:
...
26
votes
3answers
1k views
Is the concept of a clustered index in a DB design sensical when using SSDs?
When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
22
votes
3answers
9k views
N prefix before string in Transact-SQL query
Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this
SELECT * FROM ...
17
votes
4answers
2k views
SQL Server “empty table” is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
17
votes
2answers
3k views
How to execute SQL against all DBs on a Server
I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems:
select
so.name,
so.type,
MAX(case when sc.text like '%remote%' then '' ...
17
votes
4answers
1k views
Why does ORDER BY not belong in a View?
I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)
I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
16
votes
3answers
2k views
Performance difference for COALESCE versus ISNULL?
I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
16
votes
2answers
1k views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
16
votes
2answers
904 views
Guide to reading, comparing, and interpretting Execution Plans in SQL Server
I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others.
If you want ...
15
votes
2answers
500 views
T-SQL query using completely different plan depending on number of rows I'm updating
I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
14
votes
5answers
216 views
Enforcing database integrity
Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc?
How much of a performance improvement one can expect for not ...
14
votes
2answers
331 views
Why did creating this new index improve performance so much when existing index included all columns in new index?
I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems
The query in question is complicated so I'm ...
14
votes
1answer
296 views
Poor SQL Server performance when management studio closed
I've noticed a strange behaviour of SQL Server 2008 r2 express:
usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM ...
