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

SQL Server - Create a copy of a database table and place it in the same database?

I have a table ABC in a database DB. I want to create copies of ABC with names ABC_1, ABC_2, ABC_3 in the same DB. How can I do that using either Management Studio (preferably) or SQL queries ? This is for SQL Server 2008 R2.
sequel.learner
  • 3,421
  • 7
  • 22
  • 24
161
votes
8 answers

What is SYSNAME data type in SQL Server?

What is the SQL Server SYSNAME data type for? BOL says: The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. but I don't really get that. Is there a use-case you can provide?
jrara
  • 16,239
  • 33
  • 89
  • 120
161
votes
6 answers

Linq to Sql: Multiple left outer joins

I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL. I understand how to use one left outer join. I'm using VB.NET. Below is my SQL syntax. T-SQL SELECT o.OrderNumber, v.VendorName, …
Bryan Roth
  • 10,479
  • 15
  • 47
  • 56
161
votes
10 answers

SQL : BETWEEN vs <= and >=

In SQL Server 2000 and 2005: what is the difference between these two WHERE clauses? which one I should use on which scenarios? Query 1: SELECT EventId, EventName FROM EventMaster WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009' Query…
Shyju
  • 214,206
  • 104
  • 411
  • 497
160
votes
14 answers

Formatting Numbers by padding with leading zeros in SQL Server

We have an old SQL table that was used by SQL Server 2000 for close to 10 years. In it, our employee badge numbers are stored as char(6) from 000001 to 999999. I am writing a web application now, and I need to store employee badge numbers. In my new…
user153923
160
votes
13 answers

How to change identity column values programmatically?

I have a MS SQL 2005 database with a table Test with column ID. ID is an identity column. I have rows in this table and all of them have their corresponding ID auto incremented value. Now I would like to change every ID in this table like this: ID…
Tom Smykowski
  • 25,487
  • 54
  • 159
  • 236
158
votes
12 answers

Remove Identity from a column in a table

We have a 5GB table (nearly 500 million rows) and we want to remove the identity property on one of the column, but when we try to do this through SSMS - it times out. Can this be done through T-SQL?
Conrad Jagger
  • 2,223
  • 4
  • 19
  • 15
158
votes
8 answers

Check if table exists and if it doesn't exist, create it in SQL Server 2008

I am writing a Stored procedure in SQL Server 2008. I need to check if a table exists in the database. If it doesn't then I need to create it. How do I do this?
Prady
  • 10,978
  • 39
  • 124
  • 176
158
votes
3 answers

Incorrect syntax near ')' calling stored procedure with GETDATE

Maybe I am having a moment of 'afternoon', but can anyone explain why I get Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'. When running CREATE PROC DisplayDate (@DateVar DATETIME) AS BEGIN SELECT @DateVar END GO EXEC…
Nat
  • 14,175
  • 5
  • 41
  • 64
157
votes
23 answers

Find index of last occurrence of a sub-string using T-SQL

Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String.LastIndexOf method provides. A little googling…
Raj
  • 2,557
  • 2
  • 19
  • 17
156
votes
11 answers

INSERT INTO vs SELECT INTO

What is the difference between using SELECT ... INTO MyTable FROM... and INSERT INTO MyTable (...) SELECT ... FROM .... ? From BOL [ INSERT, SELECT...INTO ], I know that using SELECT...INTO will create the insertion table on the default file group…
jowenece
  • 1,860
  • 2
  • 13
  • 13
155
votes
7 answers

Can I create a One-Time-Use Function in a Script or Stored Procedure?

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a…
Mark Carpenter
  • 17,445
  • 22
  • 96
  • 149
154
votes
4 answers

Alter Table Add Column Syntax

I'm trying to programmatically add an identity column to a table Employees. Not sure what I'm doing wrong with my syntax. ALTER TABLE Employees ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1) ALTER TABLE Employees ADD CONSTRAINT …
BuddyJoe
  • 69,735
  • 114
  • 291
  • 466
154
votes
7 answers

How to select only the first rows for each unique value of a column?

Let's say I have a table of customer addresses: +-----------------------+------------------------+ | CName | AddressLine | +-----------------------+------------------------+ | John Smith | 123 Nowheresville …
nuit9
  • 1,633
  • 3
  • 14
  • 8
153
votes
19 answers

How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

How can I convert UNIX timestamp (bigint) to DateTime in SQL Server?
salman
  • 1,966
  • 3
  • 15
  • 18