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
19
votes
10 answers

HTML Encoding in T-SQL?

Is there any function to encode HTML strings in T-SQL? I have a legacy database which contains dodgey characters such as '<', '>' etc. I can write a function to replace the characters but is there a better way? I have an ASP.Net application and when…
Leo Moore
  • 2,118
  • 2
  • 19
  • 21
19
votes
4 answers

What does a timestamp in T-Sql mean in C#?

I'm trying to develop a model object to hold a Sql Server row, and I understand perfectly how to do this except for the T-Sql/SqlServer timestamp. The table is defined as: CREATE TABLE activity ( activity_id int , ip_address varchar(39) , user_id…
Cyberherbalist
  • 12,061
  • 17
  • 83
  • 121
19
votes
2 answers

Programmatically set a DB user to be db_owner

How can I assign the db_owner role to a user that I have created? I am able to create a login and add them to the database. I don't know how to change their permission to db_owner using a SQL query. I have a feeling I am maybe missing something…
Chris James
  • 11,571
  • 11
  • 61
  • 89
19
votes
10 answers

Query without WHILE Loop

We have appointment table as shown below. Each appointment need to be categorized as "New" or "Followup". Any appointment (for a patient) within 30 days of first appointment (of that patient) is Followup. After 30 days, appointment is again "New".…
LCJ
  • 22,196
  • 67
  • 260
  • 418
19
votes
3 answers

Is there an exclusive OR operator in T-SQL?

This is my statement IF (@UserName IS NULL AND @EditorKey IS NULL) OR (@UserName IS NOT NULL AND @EditorKey IS NOT NULL) BEGIN RAISERROR ('One of @UserName, @EditorKey must be non-null.', 15, 0) RETURN END What I want is to be able to do…
Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
19
votes
5 answers

Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page number. So I have something like…
Nidzo
19
votes
4 answers

How to Select a record from the database and update it in an atomic query

I have a number of records in a table with a Status column and I want to select a single record where Status = Pending and in the same atomic query mark it as Status = InProcess. What's the best way to do that? This is needed because multiple…
lahsrah
  • 9,013
  • 5
  • 37
  • 67
19
votes
3 answers

SQL Server : rename primary key

I have a table doc.MyTable which I want to deprecate by renaming to doc._MyTable. I then want to create a new doc.MyTable with the same primary key that the old doc.MyTable had. The problem is that SQL Server says that primary key already exists. So…
user3685285
  • 6,066
  • 13
  • 54
  • 95
19
votes
4 answers

t-sql get all dates between 2 dates

Possible Duplicate: Getting Dates between a range of dates Let's say I have 2 dates (date part only, no time) and I want to get all dates between these 2 dates inclusive and insert them in a table. Is there an easy way to do it with a SQL…
dcp
  • 54,410
  • 22
  • 144
  • 164
19
votes
2 answers

Pass Default value to Table Valued parameter - SQL Server

I am using a table-valued parameter in one our stored procedures. Here is the syntax I used: @districtlist NumericList readonly (NumericList is the user-defined table type). However, as a requirement I need to pass default values to this table…
bmsqldev
  • 2,627
  • 10
  • 31
  • 65
19
votes
2 answers

SQL server select distinct rows using most recent value only

I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference',…
Chris
  • 27,596
  • 25
  • 124
  • 225
19
votes
5 answers

Rounding dates to first day of the month

I am using SQL Server 2014 and I am working with a column from one of my tables, which list arrival dates. It is in the following format: ArrivalDate 2015-10-17 00:00:00.000 2015-12-03 00:00:00.000 I am writing a query that would pull data from the…
user3115933
  • 4,303
  • 15
  • 54
  • 94
19
votes
8 answers

t-sql stored procedure create scripts

I have a bunch of stored procedure names. I want to export the create script for each of the stored procedure. What is the best way to do it? Right now I am manually selecting the stored proc in SSMS and selecting "Script stored procedure as ->…
stackoverflowuser
  • 22,212
  • 29
  • 67
  • 92
19
votes
2 answers

CHARINDEX vs LIKE search gives very different performance, why?

We use Entity Frameworks for DB access and when we "think" LIKE statement - it actually generates CHARINDEX stuff. So, here is 2 simple queries, after I simplified them to prove a point on our certain server: -- Runs about 2 seconds SELECT * FROM…
katit
  • 17,375
  • 35
  • 128
  • 256
19
votes
10 answers

How to get time part from SQL Server 2005 datetime in 'HH:mm tt' format

How to get time part from SQL Server 2005 datetime in HH:mm tt format E.g. 11:25 AM 14:36 PM
Azhar
  • 20,500
  • 38
  • 146
  • 211
1 2 3
99
100