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
128
votes
4 answers

Multiple INSERT statements vs. single INSERT with multiple VALUES

I'm running a performance comparison between using 1000 INSERT statements: INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0) INSERT INTO T_TESTS (TestId, FirstName,…
Borka
  • 2,169
  • 3
  • 20
  • 26
127
votes
10 answers

Parse JSON in TSQL

Is it possible to parse JSON in TSQL? I don't mean to create a JSON string; rather, I want to parse a JSON string passed in as a parameter.
R0b0tn1k
  • 4,256
  • 14
  • 46
  • 64
127
votes
9 answers

TSQL - Cast string to integer or return default value

Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails?
Oliver Hanappi
  • 12,046
  • 7
  • 51
  • 68
127
votes
12 answers

Datatype for storing ip address in SQL Server

What datatype should I choose for storing an IP Address in a SQL Server? By selecting the right datatype would it be easy enough to filter by IP address then?
OrElse
  • 9,709
  • 39
  • 140
  • 253
126
votes
6 answers

Use of Begin / End Blocks and the Go keyword in SQL Server?

What are the guidelines as to when to use the BEGIN and END keywords in SQL Server? Also, what exactly does the GO keyword do?
Tarik
  • 79,711
  • 83
  • 236
  • 349
124
votes
20 answers

SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I have been getting the following error when running a SQL to convert my data type value from varchar to datetime. Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range…
user23495
  • 1,391
  • 2
  • 8
  • 4
124
votes
10 answers

How to group by month from Date field using sql

How can I group only by month from a date field (and not group by day)? Here is what my date field looks like: 2012-05-01 Here is my current SQL: select Closing_Date, Category, COUNT(Status)TotalCount from MyTable where Closing_Date >=…
user1858332
  • 1,915
  • 11
  • 26
  • 29
123
votes
6 answers

Return rows in random order

Is it possible to write SQL query that returns table rows in random order every time the query run?
Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
122
votes
6 answers

Using RegEx in SQL Server

I'm looking how to replace/encode text using RegEx based on RegEx settings/params below: RegEx.IgnoreCase = True RegEx.Global = True RegEx.Pattern = "[^a-z\d\s.]+" I have seen some examples on RegEx, but confused as to how to apply it…
Control Freak
  • 12,965
  • 30
  • 94
  • 145
122
votes
16 answers

Return a value if no rows are found in Microsoft tSQL

Using a Microsoft version of SQL, here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplest method to account for no…
Matt
  • 6,264
  • 10
  • 54
  • 82
122
votes
4 answers

unresolved reference to object [INFORMATION_SCHEMA].[TABLES]

I've created a UDF that accesses the [INFORMATION_SCHEMA].[TABLES] view: CREATE FUNCTION [dbo].[CountTables] ( @name sysname ) RETURNS INT AS BEGIN RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @name …
Sam
  • 40,644
  • 36
  • 176
  • 219
122
votes
31 answers

Generating random strings with T-SQL

If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?
Scott Lawrence
  • 6,993
  • 12
  • 46
  • 64
121
votes
4 answers

How To Create Table with Identity Column

I have an existing table that I am about to blow away because I did not create it with the ID column set to be the table's Identity column. Using SQL Server Management Studio, I scripted a "Create To..." of the existing table and got this: CREATE…
user153923
120
votes
4 answers

Export database schema into SQL file

Is it possible in MS SQL Server 2008 to export database structure into a T-SQL file? I want to export not only tables schema but also primary keys, foreign keys, constraints, indexes, stored procedures, user defined types/functions. Also I don't…
seeker
  • 3,255
  • 7
  • 36
  • 68
119
votes
7 answers

Add Foreign Key relationship between two Databases

I have two tables in two different databases. In table1 (in database1) there is a column called column1 and it is a primary key. Now in table2 (in database2) there is a column called column2 and I want to add it as a foreign key. I tried to add it…
Sam
  • 1,293
  • 2
  • 10
  • 8