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
20
votes
3 answers

Find recent object changes in SQL Server Database

I've added and modified several (new and existing resp.) tables and stored procs, for a particular database and server, in last 3 months. I was thinking if there's any SQL query by which I can determine all those changes. Thanks.
Kings
  • 1,551
  • 12
  • 32
  • 52
20
votes
6 answers

CONCAT_WS() for SQL Server

How can I emulate MySQL's CONCAT_WS() function in SQL Server? This function is similar to CONCAT() function in SQL Server 2012 except that it adds a separator between non-NULL items: SELECT id, CONCAT_WS('; ', a, b, c, d) AS bar FROM foo ORDER BY…
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
20
votes
3 answers

List all the databases on one SQL Server in the order they were created

I have probably in excess of 100 databases on this one SQL Server (2005) instance. I'd like to list them in order of their create dates, or even better, in the order of the date of the latest modification to any table. Is there a SELECT query I can…
John Dunagan
  • 1,445
  • 3
  • 18
  • 30
20
votes
2 answers

How to find consecutive rows based on the value of a column?

I have some data. I want to group them based on the value of data column. If there are 3 or more consecutive rows that have data bigger than 10, then those rows are what I want. So for this data: use tempdb; go set nocount on; if object_id('t',…
Just a learner
  • 26,690
  • 50
  • 155
  • 234
20
votes
7 answers

How to gain exclusive access to SQL Server 2005 database to restore?

Whenever I restore a backup of my database in SQL Server I am presented with the following error: Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 1 RESTORE…
RedWolves
  • 10,379
  • 12
  • 49
  • 68
20
votes
9 answers

IsEmpty function like ISNULL in SQL Server?

I have this sql, IF(@ID = '') BEGIN SET @ID = NULL; END IF(@Name = '') BEGIN SET @Name = NULL; END IF(@PhoneNumber = '') BEGIN SET @PhoneNumber = NULL; END IF(@Price = '') BEGIN SET @Price = NULL; END IF(@NewPrice =…
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
20
votes
8 answers

Convert number to varchar in SQL with formatting

Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting? For instance, my TINYINT has a value of 3 and I want to convert it to a VARCH of 03, so that it always shows a 2 digit number. I don't see this ability in the…
Jeff Stock
  • 3,796
  • 12
  • 46
  • 61
20
votes
2 answers

Add Column to Temp Table - 'Invalid column name DECIMAL'

I'm creating a temporary table via a SELECT INTO. I'd like to create the temp table then add a column to it like so: SELECT id, name, val INTO #TEMP_TBL ALTER TABLE #TEMP_TBL ADD new_col AS DECIMAL Error: Invalid column name 'DECIMAL'. Where…
ExceptionLimeCat
  • 6,191
  • 6
  • 44
  • 77
20
votes
4 answers

T-SQL calculating average time

I have a problem with calculating average time. This is the case: i have multiple rows, in each row there is data in time format so I need to calculate an average time of all rows and multiply it with number of rows but of course I have a problem…
user2158645
  • 201
  • 1
  • 2
  • 4
20
votes
3 answers

SQL Server 2008 - find table with most rows

Is there a way in SQL Server 2008 to find the table with the most rows in the database?
mr_dunski
  • 441
  • 1
  • 3
  • 10
20
votes
5 answers

Use of non-clustered index on guid type column in SQL Server

I would like optimize the performance of a database that my team is using for an application. I have been looking for areas to add foreign keys, and in turn index those columns to improve the performance of joins. However, many of our tables are…
Christian
  • 1,685
  • 9
  • 28
  • 48
20
votes
2 answers

nvarchar is not a recognized cursor option

I am running some sql that take the info from a temporary table and puts it into the permanent table. I got it from a step by step guide written 3 years ago, and the person who wrote it is long gone. it states to use this sql here. declare @Password…
user1469499
20
votes
4 answers

Preserve SQL Indexes While Altering Column Datatype

I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it.…
Phillip Benages
  • 661
  • 2
  • 9
  • 20
20
votes
1 answer

DayOfYear function in T-SQL / SQL Server 2000

2 FEB is the 33rd day of the year, for example. Is there a built-in T-SQL function that returns the day-of-year (in SQL Server 2000)? Or do you have to roll your own using casts to get the first day of the year for the supplied date, and then do a…
Tim
  • 8,669
  • 31
  • 105
  • 183
20
votes
11 answers

Get last Friday's Date unless today is Friday using T-SQL

I'm trying to get the correct SQL code to obtain last Friday's date. A few days ago, I thought I had my code correct. But just noticed that it's getting last week's Friday date, not the last Friday. The day I'm writing this question is Saturday,…
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245