Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
263
questions
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 ...
- 83k
482
votes
2
answers
255k
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?
- 83.3k
10
votes
5
answers
39k
views
T SQL Table Valued Function to Split a Column on commas
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.
Ex: "one,two,three,four" would return a new table ...
- 103
39
votes
5
answers
157k
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 ...
- 6,702
35
votes
7
answers
78k
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 ...
- 1,040
17
votes
1
answer
13k
views
Are we required to handle Transaction in C# Code as well as in stored procedure
Do we really require transaction handling in c# as well database store process both side
C#:
Using(transaction with transaction scope)
{
Execute stored proc;
Transaction. Complete;
}
SQL ...
- 185
27
votes
2
answers
3k
views
What does the position of the ON clause actually mean?
The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, ...
- 1,289
16
votes
4
answers
2k
views
Is a join optimized to a where clause at runtime?
When I write a query like this...
select *
from table1 t1
join table2 t2
on t1.id = t2.id
Does the SQL optimizer, not sure if that is the correct term, translate that to...
select *
from table1 t1, ...
- 263
3
votes
1
answer
2k
views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
- 15.8k
63
votes
6
answers
69k
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,
...
- 83k
40
votes
1
answer
33k
views
GO After every T-SQL statement
What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
- 503
16
votes
3
answers
12k
views
Why should a CTE start with a semi-colon?
I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does ...
- 69.7k
10
votes
2
answers
5k
views
What is the most efficient way to concatenate strings in SQL Server?
I have this code:
DECLARE @MyTable AS TABLE
(
[Month] INT,
Salary INT
);
INSERT INTO @MyTable VALUES (1,2000), (1,3100);
SELECT [Month], Salary FROM @MyTable;
Output:
I want to concat the ...
- 459
9
votes
2
answers
26k
views
Can I get a tree-structure from a self-referenced (hierarchical) table?
Given a hierarchical table like this:
CREATE TABLE [dbo].[btree]
(
id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
I would like to obtain the whole tree ...
- 23.6k
7
votes
4
answers
15k
views
SQL Server - Alter table vs drop and create
Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
- 83
42
votes
2
answers
63k
views
What is a WITH CHECK CHECK CONSTRAINT?
I have some auto-generated T-SQL, which is probably valid, but I don't really understand.
ALTER TABLE [dbo].[MyTable]
WITH CHECK
CHECK CONSTRAINT [My_FORIEGN_KEY];
I know what a foreign key ...
- 981
34
votes
3
answers
50k
views
What does OPTION FAST in SELECT statement do?
I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN:
Specifies that the query is optimized for fast ...
32
votes
2
answers
210k
views
How to use COALESCE with multiple rows and without preceding comma?
I'm trying to achieve the following:
California | Los Angeles, San Francisco, Sacramento
Florida | Jacksonville, Miami
Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, ...
- 761
23
votes
4
answers
88k
views
Is there a way to generate table create script in TSQL?
Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
- 1,305
10
votes
3
answers
12k
views
sql server: updating fields on huge table in small chunks: how to get progress/status?
We have a very large (100million row) table, and we need to update a couple of fields on it.
For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.
Will the below do ...
8
votes
1
answer
1k
views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
- 183
3
votes
1
answer
499
views
Implementing a PIVOT query
How can I pivot the following SELECT statement:
SELECT sc.CID,
sc.CodeName as OverviewText,
scRAG.CodeName as RAGStatusText
FROM StatusCode ...
- 2,032
0
votes
1
answer
4k
views
AlwaysON Availability group query very slow
I am running below tsql to find the secondary AlwaysON servers from the primary replica :
set transaction isolation level read uncommitted
set statistics time, io on
SELECT
ISNULL(agstates....
- 62.1k
79
votes
1
answer
97k
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,589
65
votes
4
answers
138k
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,245
53
votes
4
answers
291k
views
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server.
create table #table_name
(
column1 int,
column2 varchar(200)
...
)
insert into #table_name
execute ...
- 2,602
42
votes
4
answers
85k
views
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int ...
- 2,403
32
votes
3
answers
22k
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 with ...
19
votes
5
answers
33k
views
Why can't I use a CASE statement to see if a column exists and not SELECT from it?
Why does something like this not work?
SELECT
CASE
WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL
ELSE Somecol
END AS MyTest
FROM Customers;
I am just checking if the column ...
- 367
16
votes
3
answers
14k
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 ...
- 14.6k
16
votes
2
answers
16k
views
How to optimise T-SQL query using Execution Plan
I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
- 435
15
votes
3
answers
28k
views
EXEC vs SP_EXECUTESQL Performance
Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.
I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference ...
- 1,369
10
votes
1
answer
92k
views
How to extract the last inserted row in SQL Server?
When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
- 153
10
votes
2
answers
102k
views
Joining multiple tables results in duplicate rows
I am getting more rows then I expect returned by my query.
I believe it has something to do with my join statements.
There are multiple tables that have different information in them. Person ...
- 415
8
votes
1
answer
536
views
Is it "legal" to CREATE and DROP #SomeTable more than once?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:
CREATE TABLE #WidgetSetting
(...
- 6,345
6
votes
5
answers
17k
views
Creating temporary table dynamically using condition
I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional ...
- 2,063
6
votes
2
answers
3k
views
Which is better for long term database scalability: adding columns or having key/value store
For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:
to add new columns to settings table every time new ...
- 163
5
votes
1
answer
9k
views
How to set identity_insert to on in SQL Server
I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
- 351
5
votes
1
answer
34k
views
Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function
If this is not the right forum please let me know and move it for me.
I've just got a bunch of inserts like this toward the end of my scalar function:
INSERT INTO [Raptor].[dbo].[UserRole]
...
- 163
4
votes
2
answers
6k
views
How do I delete unique rows from a table with duplicate keys?
I have the following data:
road | ID | other column | other column |
------------------+------+---------------+--------------+
MAIN ST | 1 | ... | ...
- 155
4
votes
2
answers
22k
views
A Script to insert dummy Data in all tables of Database
I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
- 2,776
3
votes
2
answers
42k
views
T-SQL query for date of last full backup, size and location
I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for ...
- 652
49
votes
8
answers
332k
views
Writing select result to a csv file
We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.
I ...
- 3,674
39
votes
8
answers
81k
views
Does SQL Server support GREATEST and LEAST, if not what is the common workaround?
Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least..
least(...
- 62.2k
34
votes
3
answers
19k
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 ...
user1465095
31
votes
5
answers
99k
views
List Jobs, Schedules, and Next Scheduled Run Datetimes
List Jobs, Schedules, and Next Scheduled Run Datetimes
I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding.
sysjobactivity has ...
- 445
29
votes
1
answer
8k
views
What date/time literal formats are LANGUAGE and DATEFORMAT safe?
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:
yyyyMMdd ...
- 180k
26
votes
6
answers
3k
views
Index performance on ON versus WHERE
I have two tables
@T1 TABLE
(
Id INT,
Date DATETIME
)
@T2 TABLE
(
Id INT,
Date DATETIME
)
These tables have a non-clustered index on (Id, Date)
And I join these tables
SELECT *
...
- 387
26
votes
8
answers
48k
views
Trying to find the last time that a value has changed
I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.
Records are inserted into this table periodically. The ID will always stay the same but ...
- 1,607
25
votes
1
answer
38k
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 ...
- 1,248