Questions tagged [sql-server]
Microsoft SQL Server is a relational database management system (RDBMS). Use this tag for all Microsoft SQL Server editions including Compact, Express, Azure, Fast-track, APS (formerly PDW) and Azure SQL DW. Do not use this tag for other types of DBMS (MySQL, PostgreSQL, Oracle, etc.). Do not use this tag for issues on software and mobile development, unless it is directly related to the database.
26,453
questions
778
votes
19
answers
998k
views
Get top 1 row of each group
I have a table which I want to get the latest entry for each group. Here's the table:
DocumentStatusLogs Table
ID
DocumentID
Status
DateCreated
2
1
S1
7/29/2011
3
1
S2
7/30/2011
6
1
S1
8/02/2011
...
- 29.7k
375
votes
12
answers
194k
views
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).
In the original app, we used almost entirely ANSI-SQL compliant statements, with one ...
- 7,117
259
votes
9
answers
344k
views
SQL Server dynamic PIVOT query?
I've been tasked with coming up with a means of translating the following data:
date category amount
1/1/2012 ABC 1000.00
2/1/2012 DEF 500.00
2/1/2012 ...
- 3,056
2418
votes
48
answers
3.3m
views
How to concatenate text from multiple rows into a single text string in SQL Server
Consider a database table holding names, with three rows:
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
- 29.2k
328
votes
7
answers
160k
views
How can you represent inheritance in a database? [closed]
I'm thinking about how to represent a complex structure in a SQL Server database.
Consider an application that needs to store details of a family of objects, which share some attributes, but have ...
- 3,485
496
votes
5
answers
1.4m
views
Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
This is my example:
Id
Value
ColumnName
1
John
...
- 5,003
131
votes
7
answers
160k
views
Why do we always prefer using parameters in SQL statements?
I am very new to working with databases. Now I can write SELECT, UPDATE, DELETE, and INSERT commands. But I have seen many forums where we prefer to write:
SELECT empSalary from employee where salary ...
- 11.5k
533
votes
46
answers
954k
views
How do I split a delimited string so I can access individual items?
Using SQL Server, how do I split a string so I can access item x?
Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
- 75k
332
votes
9
answers
1.0m
views
Convert Rows to columns using 'Pivot' in SQL Server
I have read the stuff on MS pivot tables and I am still having problems getting this correct.
I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a ...
- 3,572
529
votes
57
answers
2.5m
views
Cannot Connect to Server - A network-related or instance-specific error
I get the following error when trying to connect to SQL Server:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was ...
- 5,485
1375
votes
43
answers
1.4m
views
How can I remove duplicate rows?
I need to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows).
The rows, of course, will not be perfect duplicates because of the existence of the RowID identity field.
...
- 69.5k
336
votes
18
answers
728k
views
Turning a Comma Separated string into individual rows
I have a SQL Table like this:
SomeID
OtherID
Data
abcdef-.....
cdef123-...
18,20,22
abcdef-.....
4554a24-...
17,19
987654-.....
12324a2-...
13,19,20
Is there a query where I can perform a query ...
- 179k
191
votes
27
answers
669k
views
T-SQL split string
I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select ...
- 2,157
4265
votes
40
answers
5.3m
views
How do I UPDATE from a SELECT in SQL Server?
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it ...
- 44.9k
310
votes
4
answers
102k
views
What makes a SQL statement sargable?
By definition (at least from what I've seen) sargable means that a query is capable of having the query engine optimize the execution plan that the query uses. I've tried looking up the answers, but ...
- 20k
194
votes
15
answers
322k
views
Calculate a Running Total in SQL Server
Imagine the following table (called TestTable):
id somedate somevalue
-- -------- ---------
45 01/Jan/09 3
23 08/Jan/09 5
12 02/Feb/09 0
77 14/Feb/09 7
39 20/...
- 22.9k
494
votes
23
answers
869k
views
How to use GROUP BY to concatenate strings in SQL Server?
How do I get:
id Name Value
1 A 4
1 B 8
2 C 9
to
id Column
1 A:4, B:8
2 C:9
- 15.6k
1841
votes
35
answers
2.6m
views
Insert results of a stored procedure into a temporary table
How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
select *
into ...
- 21.6k
192
votes
38
answers
1.3m
views
How to split a comma-separated value to columns
I have a table like this
Value
String
1
Cleo, Smith
I want to separate the comma delimited string into two columns
Value
Name
Surname
1
Cleo
Smith
I need only two fixed extra columns
- 2,073
379
votes
13
answers
507k
views
How do I obtain a Query Execution Plan in SQL Server?
In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?
- 85.8k
1378
votes
15
answers
1.8m
views
How do I escape a single quote in SQL Server?
I am trying to insert some text data into a table in SQL Server 9.
The text includes a single quote '.
How do I escape that?
I tried using two single quotes, but it threw me some errors.
eg. insert ...
- 15.4k
879
votes
13
answers
526k
views
Count(*) vs Count(1) - SQL Server
Just wondering if any of you people use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone ...
- 29.7k
639
votes
11
answers
1.9m
views
NOT IN vs NOT EXISTS
Which of these queries is the faster?
NOT EXISTS:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE NOT EXISTS (
SELECT 1
FROM Northwind..[Order Details] od
WHERE p....
- 16.3k
138
votes
4
answers
297k
views
How to make a query with group_concat in sql server [duplicate]
I know that in sql server we cannot use Group_concat function but here is one issue i have in which i need to Group_Concat my query.I google it found some logic but not able to correct it.My sql query ...
- 5,633
153
votes
6
answers
93k
views
Identity increment is jumping in SQL Server database
In one of my tables Fee in column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.
if it is 1205446 it ...
- 3,773
139
votes
11
answers
187k
views
T-SQL: Opposite to string concatenation - how to split string into multiple records [duplicate]
Possible Duplicate:
Split string in SQL
I have seen a couple of questions related to string concatenation in SQL.
I wonder how would you approach the opposite problem: splitting coma delimited ...
- 53.4k
323
votes
12
answers
350k
views
NULL values inside NOT IN clause
This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had ...
- 48.8k
1373
votes
15
answers
1.2m
views
How to get the identity of an inserted row?
How can I get the IDENTITY of an inserted row?
I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY, but don't understand the implications or impacts attached to each. How do these differ, and ...
- 495k
139
votes
16
answers
270k
views
Split function equivalent in T-SQL?
I’m looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a table or table variable.
Does anyone have a function that returns each one in a row?
- 10.8k
146
votes
9
answers
861k
views
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 ...
- 1,687
507
votes
4
answers
456k
views
What is the meaning of the prefix N in T-SQL statements and when should I use it?
I have seen prefix N in some insert T-SQL queries. Many people have used N before inserting the value in a table.
I searched, but I was not able to understand what is the purpose of including the N ...
- 9,413
1627
votes
19
answers
2.2m
views
How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its 'parent' table, see below:
Table: sale
id (int)
udid (int)
assid (int)
Table: ud
id (int)
assid (int)
sale.assid contains the ...
- 20.6k
229
votes
10
answers
591k
views
A table name as a variable
I am trying to execute this query:
declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename
This produces the following error:
Msg 1087, Level 16, State 1, Line 5
Must declare ...
- 15.5k
2067
votes
12
answers
2.1m
views
LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
- 21.7k
149
votes
10
answers
508k
views
Access the "previous row" value in a SELECT statement
I need to calculate the difference of a column between two lines of a table. Is there any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008.
I'm looking for something like this:
...
- 6,090
1210
votes
29
answers
2.6m
views
SQL Update from One Table to Another Based on a ID Match
I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number so that I am only working with account numbers.
I created a view ...
Boerseun
86
votes
12
answers
138k
views
What is the best way to create and populate a numbers table?
I've seen many different ways to create and populate a numbers table. However, what is the best way to create and populate one? With "best" being defined from most to least important:
Table created ...
- 103k
567
votes
21
answers
1.4m
views
Adding an identity to an existing column
I need to change the primary key of a table to an identity column, and there's already a number of rows in table.
I've got a script to clean up the IDs to ensure they're sequential starting at 1, ...
- 14.7k
505
votes
8
answers
896k
views
How Stuff and 'For Xml Path' work in SQL Server?
Table is:
Id
Name
1
aaa
1
bbb
1
ccc
1
ddd
1
eee
Required output:
Id
abc
1
aaa,bbb,ccc,ddd,eee
Query:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH (...
- 5,899
1155
votes
47
answers
1.6m
views
Exclude a column using SELECT * [except columnA] FROM tableA?
We all know that to select all columns from a table, we can use
SELECT * FROM tableA
Is there a way to exclude column(s) from a table without specifying all the columns?
SELECT * [except columnA] ...
uuɐɯǝʃǝs
131
votes
7
answers
197k
views
SQL Server: Examples of PIVOTing String data
Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a ...
- 1,866
209
votes
10
answers
172k
views
Foreign key constraint may cause cycles or multiple cascade paths?
When I try to add constraints to my tables I get the error:
Introducing FOREIGN KEY constraint 'FK74988DB24B3C886' on table 'Employee' may cause cycles or multiple cascade paths. Specify ON DELETE NO ...
Christian Nielsen
137
votes
2
answers
221k
views
ExecuteReader requires an open and available Connection. The connection's current state is Connecting
When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously:
ExecuteReader requires an open and available Connection. ...
- 1,690
67
votes
8
answers
26k
views
SQL, Auxiliary table of numbers
For certain types of sql queries, an auxiliary table of numbers can be very useful. It may be created as a table with as many rows as you need for a particular task or as a user defined function that ...
- 9,340
738
votes
22
answers
930k
views
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run ...
- 30k
365
votes
13
answers
561k
views
How to pass an array into a SQL Server stored procedure
How to pass an array into a SQL Server stored procedure?
For example, I have a list of employees. I want to use this list as a table and join it with another table. But the list of employees should ...
- 8,013
107
votes
5
answers
337k
views
Comma separated results in SQL
I have the following code which will create a comma delimited list for my results:
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+', ' ,'') + INSTITUTIONNAME
FROM EDUCATION
WHERE ...
- 5,251
110
votes
4
answers
236k
views
ListAGG in SQLSERVER
I'm trying to aggregate a 'STRING' field in SQLServer. I would like to find the same function LISTAGG like in Oracle .
Do you know how to do the same function or an another method?
For Example,
...
- 1,123
1792
votes
30
answers
4.6m
views
How do I perform an IF...THEN in an SQL SELECT?
How do I perform an IF...THEN in an SQL SELECT statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
- 29.8k
982
votes
13
answers
1.1m
views
Update a table using JOIN in SQL Server?
I want to update a column in a table making a join on other table e.g.:
UPDATE table1 a
INNER JOIN table2 b ON a.commonfield = b.[common field]
SET a.CalculatedColumn= b.[Calculated Column]
WHERE
...
- 11.4k