Questions tagged [tsql]

T-SQL (Transact Structured Query Language) is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server. Do not use this tag for MySQL, PostgreSql, Oracle(Pl/SQL) related queries. Please note that SQL code which is being written using LINQ will not also be the part of this tag. This tag specifically created for advanced SQL programming using Microsoft SQL Server.

When to use this tag:
Use this tag for SQL questions for or .
Do not use this tag for questions about other database vendors, since T-SQL is only used by these two products.

Questions about T-SQL should be tagged appropriately with either or , and include the version as well.

How to write a good T-SQL question:
There are five ingredients to this recipe:

  1. Provide a clear textual explanation of the problem at hand.
  2. Provide proper sample data, as DDL (Create table statement(s)) and DML (Insert statement(s)). The sample data should be enough to demonstrate the problem, including edge cases. Usually, a few relevant rows are enough. (You can use SQLFiddle as an online tool to generate DDL and DML statements for you from tabular data using their Text To DDL button.)
  3. Provide expected output for the sample data you've posted.
  4. Show your attempts to solve the problem.
  5. Tag properly. In addition to , also provide the product tag (either or ), and the lowest version you need the solution for.
    (i.e. If you're developing on SQL Server 2016, but the production server is SQL Server 2012, Use .)

Do not include images of data or code!

A link to an online SQL test environment such as SQL Fiddle or DB Fiddle might help, but it is not a replacement for having all the data inside the question.
For more information, Read Why should I provide an MCVE for what seems to me to be a very simple SQL query? and Help me write this query in SQL

General Information

T-SQL (Transact Structured Query Language) is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server. They share the name because Microsoft SQL Server was based on Sybase ASE code in a partnership that later dissolved. While the name is shared, functionality is not necessarily identical. With the introduction of Microsoft SQL Server 2005, the SQL Server code was completely re-written to provide integration with .Net.

Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

It adheres to nearly all SQL standards, yet supplies an additional set of functionality not a part of the SQL standard, with concepts such as stored procedure, triggers , common table expressions, index hints, and the ability to SELECT scalars.

More information can be found in SQL Server Transact-SQL Reference and Sybase Transact-SQL User's Guide.

Free online T-SQL courses

72684 questions
20
votes
11 answers

Convert getdate() to EST

I would like to convert getdate() in SQL Server to EST time.
derin
  • 201
  • 1
  • 2
  • 3
20
votes
8 answers

Detecting dirty reads from a stored procedure

I've got 100 threads that are each calling the stored procedure as defined below. How do I prevent dirty reads? SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO ALTER procedure GetNextCerealIdentity (@NextKey int output, @TableID int) AS …
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
20
votes
1 answer

TSQL RaiseError incorrect syntax, following MSDN's guidelines

MSDN states the following syntax: RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ] The msg_str expects a string up to 2047 characters but truncates longer strings. It…
Aske B.
  • 6,419
  • 8
  • 35
  • 62
20
votes
3 answers

SQL error stating invalid column name when I have verification if it exists. Why?

There is staging script, which creates new column DOCUMENT_DEFINITION_ID stages it with values of MESSAGE_TYPE_ID + 5 and then removes column MESSAGE_TYPE_ID. First time everything run ok, but when I run script second time I'm getting this…
Andriy Buday
  • 1,959
  • 1
  • 17
  • 40
20
votes
7 answers

Why to use "not null primary key" in TSQL?

I am seeing too frequently "not null primary key" in scripts creating tables in TSQL in blogs, articles, books, forums, here in SO, etc. For example, BING gives 630,000 results ((just in English) if to search against "not null" NEAR "primary key"…
20
votes
4 answers

How do I extract part of a string in t-sql

If I have the following nvarchar variable - BTA200, how can I extract just the BTA from it? Also, if I have varying lengths such as BTA50, BTA030, how can I extract just the numeric part?
Xaisoft
  • 45,655
  • 87
  • 279
  • 432
20
votes
2 answers

Retrieving XML element name using t-SQL

If I have: john something or other mary random stuff How do I get a list of the element names 'name' and…
Matt W
  • 11,753
  • 25
  • 118
  • 215
20
votes
2 answers

Why is variable declared inside IF statement created even when condition evaluates to false?

Since @A is never declared, sql server should throw an error, but it doesn’t. Why is that? DECLARE @i int = 1; IF @i > 10 BEGIN DECLARE @A int = 100; END PRINT @A; // doesn't return any result thanx
AspOnMyNet
  • 1,958
  • 4
  • 22
  • 39
20
votes
5 answers

Update a single row with t-sql

I want to update a row in my date base. The problem is, through a mistake on my part, I have two identical rows of data. How do I run the update on just one row?
dan_vitch
  • 4,477
  • 12
  • 46
  • 69
20
votes
3 answers

What's the meaning of the GO-statement in TSQL

I'm new to TSQL and wondering what the GO statement really means. To me it just seems thrown in there where ever it seems to fit. I guess it somehow tells sql server to run the previous statement? What happens if you don't use them at all? Could…
picknick
  • 3,897
  • 6
  • 33
  • 48
20
votes
5 answers

Getting maximum value of float in SQL programmatically

Is there an method for programmatically (in T-SQL) retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValue in C#. I would like to use it in some selection when the parameter does not equal any actual values in…
Axarydax
  • 16,353
  • 21
  • 92
  • 151
20
votes
4 answers

How to loop through all SQL tables?

We have a piece of software that does not delete entries we no longer want. In order to get a feel for how much data is wasting away in our server and prepare for a big cleanup operation, I am trying to loop through all of the tables and pull the…
Jonathon Anderson
  • 1,162
  • 1
  • 8
  • 24
20
votes
7 answers

Pass a variable into a trigger

I have a trigger which deals with some data for logging purposes like so: CREATE TRIGGER trgDataUpdated ON tblData FOR UPDATE AS BEGIN INSERT INTO tblLog ( ParentID, OldValue, NewValue, UserID ) SELECT deleted.ParentID, deleted.Value,…
Codesleuth
  • 10,321
  • 8
  • 51
  • 71
20
votes
5 answers

IS NULL vs = NULL in where clause + SQL Server

How to check a value IS NULL [or] = @param (where @param is null) Ex: Select column1 from Table1 where column2 IS NULL => works fine If I want to replace comparing value (IS NULL) with @param. How can this be done Select column1 from Table1 where…
Sreedhar
  • 29,307
  • 34
  • 118
  • 188
20
votes
8 answers

Why is doing a top(1) on an indexed column in SQL Server slow?

I'm puzzled by the following. I have a DB with around 10 million rows, and (among other indices) on 1 column (campaignid_int) is an index. Now I have 700k rows where the campaignid is indeed 3835 For all these rows, the connectionid is the same. I…
Toad
  • 15,593
  • 16
  • 82
  • 128