Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,046
questions
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
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.9k
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
137k
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
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,
...
- 82.9k
53
votes
2
answers
6k
views
How to create Unicode parameter and variable names
All of this works:
CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯...
- 42.7k
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
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
46
votes
9
answers
7k
views
Asked to Not Use Transactions and to Use A Workaround to Simulate One
I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have ...
- 401
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
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
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
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.1k
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
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
34
votes
1
answer
47k
views
How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?
I want to set a Unicode string variable to particular character based on its Unicode code point.
I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
- 1,853
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 ...
34
votes
1
answer
93k
views
Decode Base64 String Natively in SQL Server
I have a varchar column in a table in SQL Server that holds a Base64-encoded text string,
which I would like to decode into its plain text equivalent.
Does SQL Server have any native functionality to ...
- 2,777
32
votes
5
answers
94k
views
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
One of INSERT INTO script is written as following.
INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),....
Following is the error we are facing on ...
- 2,895
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 ...
32
votes
2
answers
209k
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
31
votes
5
answers
98k
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
31
votes
1
answer
1k
views
Why is the speed of executing statements dependent on the network connection?
It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it ...
- 22k
31
votes
2
answers
48k
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
...
- 517
29
votes
3
answers
27k
views
If positive, sum all items. If negative, return each one
I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL:
Create ...
- 960
29
votes
1
answer
18k
views
Why 0 is equal to empty string?
I need some help in finding why the following T-SQL statement returns 1 (true):
SELECT IIF( 0 = '', 1, 0)
I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
- 3,770
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
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
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
26
votes
4
answers
71k
views
Limit results to the first 2 ranking rows
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, ...
- 937
26
votes
1
answer
78k
views
Insert results from a stored procedure into a table variable
I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.
I am trying to set these return values in another table variable ...
- 397
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
24
votes
9
answers
9k
views
Test if a string is a palindrome using T-SQL
I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an ...
- 875
24
votes
4
answers
5k
views
If a database only ever has one insert, is it bad to index every possible column combination?
I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is ...
- 365
24
votes
4
answers
42k
views
Can I change table structure in a transaction and then roll it back if there is an error?
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
- 949
24
votes
4
answers
21k
views
Choosing the right algorithm in HashBytes function
We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario?
We want to ensure ...
- 3,674
23
votes
3
answers
13k
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, ...
- 559
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
23
votes
2
answers
74k
views
How to check for Non-Ascii Characters
What is the best way to check if a VARCHAR field has Non-Ascii Characters?
CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255).
I tried using PATINDEX and have run into the following issue.
...
- 333
22
votes
6
answers
9k
views
SQL Server injection - how much damage in 26 chars?
I'm testing for resilience against injection attacks on an SQL Server database.
All table names in the db are lower case, and the collation is case-sensitive, Latin1_General_CS_AS.
The string I can ...
- 417
22
votes
2
answers
308k
views
Update table using values from another table in SQL Server
I have 2 table in my database.
Table #1
-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-----------...
- 351
21
votes
6
answers
119k
views
How to create a row for every day in a date range using a stored procedure?
I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs - A start date and end date of the date range ...
- 375
21
votes
2
answers
41k
views
Select all records, join with table A if join exists, table B if not
So here's my scenario:
I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to ...
- 1,513
21
votes
1
answer
7k
views
Why is my ORDER BY in STRING_AGG not always working?
I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.
CREATE TABLE HashTable(
RecordID VARCHAR(255),
...
- 321
21
votes
1
answer
15k
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 ...
- 2,591
20
votes
2
answers
5k
views
Can't update "CO2" to "CO₂" in table row
Given this table:
CREATE TABLE test (
id INT NOT NULL,
description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');
I've realised ...
- 1,079
20
votes
4
answers
5k
views
Why can't I use variables in T-SQL like I imagine I can?
Forgive me, I am a developer who has moved over to the world of SQL. I thought I could improve some SQL by adding variables but it did not function like I expected. Can someone tell me why this does ...
- 369
20
votes
2
answers
6k
views
DEFAULT CONSTRAINT, worth it?
I usually design my databases following next rules:
Nobody else than db_owner and sysadmin have access to the database tables.
User roles are controlled at application layer. I usually use one db ...
- 23.6k