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
118
votes
8 answers

In SQL Server, what does "SET ANSI_NULLS ON" mean?

The definition says: When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if…
Rodniko
  • 4,926
  • 20
  • 69
  • 93
118
votes
13 answers

Cannot execute script: Insufficient memory to continue the execution of the program

I have a 123MB sql file which I need to execute in my local PC. But I am getting Cannot execute script: Insufficient memory to continue the execution of the program How to solve this issue?
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
118
votes
10 answers

SQL Row_Number() function in Where Clause

I found one question answered with the Row_Number() function in the where clause. When I tried one query, I was getting the following error: "Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY…
Joseph
118
votes
6 answers

Partition Function COUNT() OVER possible using DISTINCT

I'm trying to write the following in order to get a running total of distinct NumUsers, like so: NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth]) Management studio doesn't seem too happy about this. The error disappears when I…
whytheq
  • 34,466
  • 65
  • 172
  • 267
117
votes
11 answers

How to Concatenate Numbers and Strings to Format Numbers in T-SQL?

I have the following function ALTER FUNCTION [dbo].[ActualWeightDIMS] ( -- Add the parameters for the function here @ActualWeight int, @Actual_Dims_Lenght int, @Actual_Dims_Width int, @Actual_Dims_Height int ) RETURNS…
Jack
  • 9,843
  • 23
  • 78
  • 111
117
votes
6 answers

Printing integer variable and string on same line in SQL

Ok so I have searched for an answer to this on Technet, to no avail. I just want to print an integer variable concatenated with two String variables. This is my code, that doesn't run: print 'There are ' + @Number + ' alias combinations did not…
user2993456
117
votes
4 answers

Replace Default Null Values Returned From Left Outer Join

I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way…
Brett Bim
  • 3,190
  • 4
  • 28
  • 26
116
votes
11 answers

sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? Reason I ask is because in MS SQL Server I can create an index on this…
danifo
  • 1,635
  • 3
  • 13
  • 12
116
votes
2 answers

alternatives to REPLACE on a text or ntext datatype

I need to update/replace the data in datatable.column. The table has a field named Content. I'm using the REPLACE function. Since the column datatype is NTEXT, SQL Server doesn't allow me to use the REPLACE function. I can't change the datatype…
ethem
  • 2,888
  • 9
  • 42
  • 57
116
votes
2 answers

How to force a SQL Server 2008 database to go Offline

How do I force my Database to go Offline, without regard to what or who is already using it? I tried: ALTER DATABASE database-name SET OFFLINE; But it's still hanging after 7 min. I want this because I need to test the scenario. If it's even…
radbyx
  • 9,352
  • 21
  • 84
  • 127
116
votes
6 answers

Aggregate function in an SQL update query?

I'm trying to set the value in one table to the sum of the values in another table. Something along these lines: UPDATE table1 SET field1 = SUM(table2.field2) FROM table1 INNER JOIN table2 ON table1.field3 = table2.field3 GROUP BY table1.field3 Of…
Margaret
  • 5,749
  • 20
  • 56
  • 72
115
votes
7 answers

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

I am getting following error in my SQL server 2008 R2 database: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tblArmy' because it is not full-text indexed.
DotnetSparrow
  • 27,428
  • 62
  • 183
  • 316
115
votes
21 answers

What's the best way to select the minimum value from several columns?

Given the following table in SQL Server 2005: ID Col1 Col2 Col3 -- ---- ---- ---- 1 3 34 76 2 32 976 24 3 7 235 3 4 245 1 792 What is the best way to write the query that yields the…
stucampbell
  • 6,383
  • 5
  • 26
  • 25
115
votes
9 answers

How to get current instance name from T-SQL

How can I get the SQL Server server and instance name of the current connection, using a T-SQL script?
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
115
votes
8 answers

COALESCE Function in TSQL

Can someone explain how the COALESCE function in TSQL works? The syntax is as follows COALESCE(x, y) The MSDN document on this function is pretty vague
Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248