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
153
votes
17 answers

How to count instances of character in SQL Column

I have an sql column that is a string of 100 'Y' or 'N' characters. For example: YYNYNYYNNNYYNY... What is the easiest way to get the count of all 'Y' symbols in each row.
cindi
  • 4,571
  • 8
  • 31
  • 38
153
votes
11 answers

How do you copy a record in a SQL table but swap out the unique id of the new row?

This question comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this: insert into MyTable select * from MyTable…
Kilhoffer
  • 32,375
  • 22
  • 97
  • 124
151
votes
11 answers

Base64 encoding in SQL Server 2005 T-SQL

I'd like to write a T-SQL query where I encode a string as a Base64 string. Surprisingly, I can't find any native T-SQL functions for doing Base64 encoding. Does a native function exist? If not, what's the best way to do Base64 encoding in T-SQL?
Jacob
  • 77,566
  • 24
  • 149
  • 228
151
votes
4 answers

How to generate a Guid in SQL Server?

I need to create an Id as Guid in SQL(no identity) How I can do this? I defined the Id as uniqueidentifier but what is save in Db is 00000000-0000-0000-0000-000000000000
Alma
  • 3,780
  • 11
  • 42
  • 78
151
votes
2 answers

Is there a ternary conditional operator in T-SQL?

What are alternatives to implement the following query: select * from table where isExternal = @type = 2 ? 1 : 0
Anthony
  • 2,715
  • 5
  • 26
  • 34
150
votes
10 answers

Delete all the records

How to delete all the records in SQL Server 2008?
kaveh
  • 1,509
  • 2
  • 9
  • 3
149
votes
12 answers

Differences between MySQL and SQL Server

I'm an ASP.NET developer who has used Microsoft SQL Server for all my database needs (both at work and for personal projects). I am considering trying out the LAMP stack for some of my personal projects. What are some of the main differences…
sestocker
  • 3,522
  • 6
  • 27
  • 32
148
votes
9 answers

What does collation mean?

What does collation mean in SQL, and what does it do?
LOVE_KING
  • 1,501
  • 3
  • 12
  • 8
148
votes
5 answers

T-SQL stored procedure that accepts multiple Id values

Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure? For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the…
JasonS
  • 23,480
  • 9
  • 41
  • 46
148
votes
3 answers

Unpivot with column name

I have a table StudentMarks with columns Name, Maths, Science, English. Data is like Name, Maths, Science, English Tilak, 90, 40, 60 Raj, 30, 20, 10 I want to get it arranged like the following: Name, Subject, …
Tilak
  • 30,108
  • 19
  • 83
  • 131
146
votes
8 answers

IDENTITY_INSERT is set to OFF - How to turn it ON?

I have a deleted file archive database that stores the ID of the file that was deleted, I want the admin to be able to restore the file (as well as the same ID for linking files). I do not want to take identity_insert off the entire table, as the…
Spooks
  • 6,937
  • 11
  • 49
  • 66
144
votes
9 answers

Simple way to transpose columns and rows in SQL?

How do I simply switch columns with rows in SQL? Is there any simple command to transpose? ie turn this result: Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 …
edezzie
  • 1,657
  • 4
  • 14
  • 12
143
votes
18 answers

How to print VARCHAR(MAX) using Print Statement?

I have a code which is: DECLARE @Script VARCHAR(MAX) SELECT @Script = definition FROM manged.sys.all_sql_modules sq where sq.object_id = (SELECT object_id from managed.sys.objects Where type = 'P' and Name = 'usp_gen_data') Declare @Pos…
peter
  • 2,396
  • 6
  • 24
  • 29
142
votes
4 answers

What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?
Goran
  • 1,744
  • 3
  • 12
  • 12
142
votes
5 answers

How to take last four characters from a varchar?

I'm trying to take the last four characters only from a varchar field. All the rows are different lengths. What function should I be using to accomplish this?
Michael A
  • 9,480
  • 22
  • 70
  • 114