Transact-SQL (t-sql) is a dialect of SQL used by Micrsoft's [SQL Server](http://www.microsoft.com/sqlserver/en/us/default.aspx) and SAP's [Sybase](http://www.sybase.com/).
117
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?
15
votes
7answers
12k 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 ...
13
votes
3answers
2k views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
11
votes
2answers
1k 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 ...
10
votes
5answers
812 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 ...
10
votes
2answers
1k views
Good way to call multiple SQL Server Agent jobs sequentially from one main job?
I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
10
votes
1answer
378 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
9
votes
2answers
438 views
Why does SELECT 1/2 return 0?
As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.
I am using SQL Server 2008.
9
votes
5answers
579 views
UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)
I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID ...
9
votes
3answers
167 views
Unique Identifier with Extra Characters Still Matching in Select
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.
For ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
8
votes
1answer
316 views
Forcing an index spool
I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear.
However, I end up with a ...
8
votes
2answers
149 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
2answers
886 views
In Sql Server's RANK function, how to limit the results to just the first 3 ranks
In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC)
to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
8
votes
2answers
240 views
How does SQL Server store the information in Job Schedule Properties?
I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run.
I want to know how this is all determined and stored. So where does it store the ...
8
votes
1answer
249 views
Generating large strings for test data
I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
7
votes
2answers
1k views
Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.
As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
7
votes
3answers
234 views
When are procedural queries absolutely necessary?
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
7
votes
3answers
866 views
Using Row_Number to find consecutive row count
I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row
If my data looks like this
724
727
728
733
735
737
743
747
...
7
votes
2answers
147 views
How to flatten results of a table with two related “many” tables?
I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.
I have the following tables (somewhat abbreviated ...
7
votes
2answers
1k views
Subqueries' aliases same as main queries' aliases
I have an SQL query whose aliases are the same as some of its subquery's aliases.
For example:
select *
from ROOM r
where ...
(
select *
from ROAD r
...
7
votes
1answer
92 views
Trouble with SQL and aggregates
With this query:
SELECT SUM(qty) AS sumQty,
cnetprodid
FROM quoteitem
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
...
7
votes
1answer
60 views
sysschedules datatype choices
I am Curious.
Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000.
Was it a ...
7
votes
3answers
1k views
TSQL performance - JOIN on value BETWEEN min and max
I have two tables in which I store:
an IP range - country lookup table
a list of requests coming from different IPs
The IPs were stored as bigints to improve lookup performance.
This is the table ...
7
votes
1answer
244 views
Variable for element name
I have this tSQL code which works OK:
SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
FROM [dnn].[dbo].[EventLog2] c
cross apply ...
6
votes
2answers
265 views
Is there any difference between putting a column alias at the start or the end of the column definition?
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get ...
6
votes
4answers
261 views
Returning rows where all non-NULL values are equal
Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:
WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL
this ...
6
votes
1answer
617 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 ...
6
votes
1answer
266 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
6
votes
2answers
350 views
Is there a better way to create dynamic time averaged series over a dynamic frequency?
I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
6
votes
1answer
288 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
5
votes
3answers
2k views
SQL Server 2008 merge statement deadlocking itself
I have the following procedure (SQL Server 2008r2):
create procedure usp_SaveCompanyUserData
@companyId bigint,
@userId bigint,
@dataTable tt_CoUserdata readonly
as
begin
set ...
5
votes
2answers
235 views
T-SQL Purpose of MAX in this Group By Query
I have run into some pre-existing SQL that I'm having a hard time uderstanding.
SELECT
MAX(I.Symbol) Symbol
, MAX(I.Ticker) CUSIP
, MAX(I.Name) Name
, SUM(H.Quantity) TotalQuantity
, ...
5
votes
2answers
273 views
T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs
I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
5
votes
1answer
90 views
Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?
The Setup:
Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.
Now the Transaction Log grows so quickly during the ...
5
votes
3answers
1k views
Which problems arise, declaring the size of all varchar parameters as max in stored proc?
Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.
Increasing just the size of a column in a table is a rather simple task.
...
5
votes
1answer
538 views
Should I add SET NOCOUNT ON to all my triggers?
It is fairly common knowledge that you should have
SET NOCOUNT ON
by default when creating new stored procedures.
Microsoft has changed the default template to include this in 2012. I thought ...
5
votes
1answer
197 views
difference of left join and left outer join
In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
5
votes
4answers
398 views
Create a plan guide to cache (lazy spool) CTE result
I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
5
votes
2answers
624 views
The order of INNER JOIN's
I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.
I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
1answer
2k views
Find all ancestor nodes of a HierarchyId using a single SQL statement?
I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
5
votes
2answers
692 views
How to change SQL sever configuration manager settings using TSQL?
SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc...
Is it possible to make these changes that is done in SQL server ...
5
votes
1answer
110 views
Unexpected Table Scan with Parameterized LIKE
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
5
votes
3answers
410 views
Total of orders open on a given date for each date in a date range
This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.
I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
5
votes
2answers
616 views
Grouping records based on intervals of time
I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
5
votes
3answers
449 views
How do I row count into groups?
I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
5
votes
1answer
368 views
Why can the target of the OUTPUT INTO clause not be a view?
Here is my statement:
DELETE FROM table1
OUTPUT deleted.col1, deleted.col2
INTO view1;
It gives me this error:
The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
5
votes
3answers
1k views
RETURN statement before DEALLOCATE CURSOR
Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure
Consider this example:
CREATE PROCEDURE [dbo].[test]
@ReturnEarly BIT = 0
AS
BEGIN
SET NOCOUNT ...
5
votes
1answer
646 views
How to detach corrupt database in SQL Server
I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database.
Is this safe way to do that:
DECLARE ...
5
votes
1answer
381 views
Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?
When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
