Skip to main content

Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
487 votes
2 answers
258k 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?
Martin Smith's user avatar
  • 85.4k
105 votes
6 answers
79k 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 ...
Paul White's user avatar
  • 87k
79 votes
1 answer
100k 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 ...
KutuluMike's user avatar
  • 1,589
65 votes
4 answers
144k 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 ...
Juan Velez's user avatar
  • 3,253
63 votes
6 answers
70k 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, ...
Paul White's user avatar
  • 87k
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 [¯...
Brent Ozar's user avatar
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 ...
Just a learner's user avatar
50 votes
8 answers
334k 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 ...
Sky's user avatar
  • 3,694
46 votes
9 answers
8k 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 ...
Forrest's user avatar
  • 401
45 votes
2 answers
67k 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 ...
BanksySan's user avatar
  • 1,011
42 votes
4 answers
88k 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 ...
adopilot's user avatar
  • 2,413
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 ...
TheIdiot's user avatar
  • 503
39 votes
8 answers
88k 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(...
Evan Carroll's user avatar
  • 63.9k
39 votes
5 answers
160k 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 ...
garik's user avatar
  • 6,752
36 votes
7 answers
80k 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 ...
jsauni's user avatar
  • 1,050
35 votes
2 answers
100k 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 ...
GWR's user avatar
  • 2,827
35 votes
1 answer
49k 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 ...
Riley Major's user avatar
  • 1,903
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 ...
user avatar
34 votes
3 answers
52k 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 ...
Matthew Verstraete's user avatar
33 votes
5 answers
101k 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 ...
Aasim Abdullah's user avatar
32 votes
6 answers
114k 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 ...
Kennah's user avatar
  • 455
32 votes
3 answers
23k 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 ...
Anonymous Maximus's user avatar
31 votes
2 answers
213k 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, ...
user2732180's user avatar
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 ...
Mikael Eriksson's user avatar
31 votes
2 answers
49k 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 ...
IcySnow's user avatar
  • 517
29 votes
3 answers
28k 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 ...
user2676140's user avatar
29 votes
1 answer
19k 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 ...
gotqn's user avatar
  • 4,026
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 ...
Aaron Bertrand's user avatar
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, ...
boot4life's user avatar
  • 1,279
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 * ...
Erik Bergstedt's user avatar
26 votes
8 answers
50k 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 ...
SqlSandwiches's user avatar
26 votes
4 answers
73k 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, ...
UB01's user avatar
  • 937
26 votes
1 answer
81k 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 ...
ThunD3eR's user avatar
  • 397
25 votes
4 answers
44k 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 ...
Piers Karsenbarg's user avatar
25 votes
1 answer
40k 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 ...
Jānis's user avatar
  • 1,258
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 ...
MSIS's user avatar
  • 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 ...
Lopsided's user avatar
  • 365
24 votes
2 answers
79k 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. ...
Gerhard Weiss's user avatar
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 ...
Sky's user avatar
  • 3,694
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, ...
Greg B's user avatar
  • 559
23 votes
2 answers
319k 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 | -----------...
John Doe's user avatar
  • 361
23 votes
4 answers
90k 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 ...
Andrew Savinykh's user avatar
23 votes
1 answer
5k views

Is "+" slower than "CONCAT" for large strings?

I have always thought that CONCAT function is actually a wrapper over the + (String Concatenation) with some additional checks in order to make our life more easier. I have not found any internal ...
gotqn's user avatar
  • 4,026
22 votes
2 answers
5k views

Why is COALESCE not a function?

The docs insist that ISNULL is a function, but COALESCE is not. Specifically, they say The ISNULL function and the COALESCE expression If I put on my Lisp hat, I can think of COALESCE as a macro and ...
J. Mini's user avatar
  • 555
21 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 ...
Alan B's user avatar
  • 407
21 votes
6 answers
127k 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 ...
Rob V's user avatar
  • 375
21 votes
2 answers
42k 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 ...
Der Kommissar's user avatar
21 votes
1 answer
11k 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), ...
Rebecca's user avatar
  • 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 ...
DamagedGoods's user avatar
  • 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 ...
Álvaro González's user avatar

1
2 3 4 5
63