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
251
votes
13 answers

How to calculate percentage with a SQL statement

I have a SQL Server table that contains users & their grades. For simplicity's sake, lets just say there are 2 columns - name & grade. So a typical row would be Name: "John Doe", Grade:"A". I'm looking for one SQL statement that will find the…
Alex
  • 3,719
  • 4
  • 26
  • 25
250
votes
14 answers

How can a LEFT OUTER JOIN return more records than exist in the left table?

I have a very basic LEFT OUTER JOIN to return all results from the left table and some additional information from a much bigger table. The left table contains 4935 records yet when I LEFT OUTER JOIN it to an additional table the record count is…
Jay Wilde
  • 2,714
  • 3
  • 20
  • 19
250
votes
15 answers

How do I format a number with commas in T-SQL?

I'm running some administrative queries and compiling results from sp_spaceused in SQL Server 2008 to look at data/index space ratios of some tables in my database. Of course I am getting all sorts of large numbers in the results and my eyes are…
mattmc3
  • 17,595
  • 7
  • 83
  • 103
249
votes
8 answers

SQL variable to hold list of integers

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012. One of the parameters the report asks for is a list of integers. This is achieved on the report through a multi-select…
ErickTreetops
  • 3,189
  • 4
  • 27
  • 37
248
votes
9 answers

Referring to a Column Alias in a WHERE Clause

SELECT logcount, logUserID, maxlogtm , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff FROM statslogsummary WHERE daysdiff > 120 I get "invalid column name daysdiff". Maxlogtm is a datetime field. It's the little stuff that drives me crazy.
user990016
  • 3,208
  • 4
  • 20
  • 29
248
votes
25 answers

SQL Server String or binary data would be truncated

I am involved in a data migration project. I am getting the following error when I try to insert data from one table into another table (SQL Server 2005): Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The source…
Jim Evans
  • 6,285
  • 10
  • 37
  • 60
246
votes
10 answers

Changing the maximum length of a varchar column?

I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to use to do…
Michael A
  • 9,480
  • 22
  • 70
  • 114
246
votes
16 answers

SQL: IF clause within WHERE clause

Is it possible to use an IF clause within a WHERE clause in MS SQL? Example: WHERE IF IsNumeric(@OrderNumber) = 1 OrderNumber = @OrderNumber ELSE OrderNumber LIKE '%' + @OrderNumber + '%'
Bryan Roth
  • 10,479
  • 15
  • 47
  • 56
245
votes
19 answers

How to get Time from DateTime format in SQL?

I want to get only Time from DateTime column using SQL query using SQL Server 2005 and 2008 Default output: AttDate == 2011-02-09 13:09:00 2011-02-09 14:10:00 I'd like this output: AttDate Time…
Jig12
  • 2,977
  • 4
  • 23
  • 28
243
votes
10 answers

How to update two tables in one statement in SQL Server 2005?

I want to update two tables in one go. How do I do that in SQL Server 2005? UPDATE Table1, Table2 SET Table1.LastName='DR. XXXXXX', Table2.WAprrs='start,stop' FROM Table1 T1, Table2 T2 WHERE T1.id = T2.id AND T1.id =…
Jango
  • 5,375
  • 14
  • 57
  • 63
243
votes
3 answers

Use variable with TOP in select statement in SQL Server without making it dynamic

declare @top int set @top = 5 select top @top * from tablename Is it possible? Or any idea for such a logic (i don't want to use dynamic query)?
Paresh
  • 3,659
  • 6
  • 27
  • 32
243
votes
12 answers

Change Schema Name Of Table In SQL

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ? Example: FROM dbo.Employees TO exe.Employees I tried with this query: ALTER…
theChampion
  • 4,207
  • 7
  • 28
  • 35
242
votes
33 answers

Convert Month Number to Month Name Function in SQL

I have months stored in SQL Server as 1,2,3,4,...12. I would like to display them as January,February etc. Is there a function in SQL Server like MonthName(1) = January? I am trying to avoid a CASE statement, if possible.
Saif Khan
  • 18,402
  • 29
  • 102
  • 147
240
votes
18 answers

Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?
BumbleBee
  • 10,429
  • 20
  • 78
  • 123
237
votes
5 answers

How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486