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.
sql-server
334,558
questions
4264
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
3263
votes
45
answers
3.7m
views
How to add a column with a default value to an existing table in SQL Server?
How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
- 33.8k
2416
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
2171
votes
33
answers
1.6m
views
How to check if a column exists in a SQL Server table
I need to add a specific column if it does not exist. I have something like the following, but it always returns false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ...
- 22.1k
2066
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
2017
votes
4
answers
3.4m
views
Inserting multiple rows in a single SQL query? [duplicate]
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("...
rits
1838
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
1831
votes
41
answers
561k
views
Table Naming Dilemma: Singular vs. Plural Names [closed]
Academia has it that table names should be the singular of the entity that they store attributes of.
I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table ...
1791
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.7k
1748
votes
36
answers
4.0m
views
Find all tables containing column with specified name
Is it possible to query for table names which contain columns being
LIKE '%myName%'
- 29.2k
1738
votes
14
answers
2.1m
views
How can I delete using INNER JOIN with SQL Server?
I want to delete using INNER JOIN in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE
FROM WorkRecord2
INNER ...
- 18k
1703
votes
22
answers
1.5m
views
What is the difference between varchar and nvarchar?
Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?
- 43.5k
1692
votes
28
answers
2.2m
views
Get size of all tables in database
I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.
Is there an easy way to determine how much space on disk each ...
- 18.8k
1622
votes
19
answers
2.1m
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
1435
votes
7
answers
981k
views
Difference between JOIN and INNER JOIN
Both these joins will give me the same results:
SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK
vs
SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK
Is there ...
- 163k
1432
votes
30
answers
1.9m
views
Check if table exists in SQL Server
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.
Here are two possible ways of doing it. Which one is the standard/best ...
- 22.6k
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
1375
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.3k
1371
votes
14
answers
1.6m
views
Altering a column: null to not null
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing ...
- 17.7k
1370
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 ...
- 494k
1288
votes
12
answers
889k
views
What do Clustered and Non-Clustered index actually mean?
I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes.
I googled and what I found was :
A clustered index is a ...
- 18.7k
1208
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
1149
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
1134
votes
15
answers
985k
views
When should I use CROSS APPLY over INNER JOIN?
What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. ...
- 38.4k
1104
votes
24
answers
2.4m
views
Search text in stored procedure in SQL Server
I want to search a text from all my database stored procedures. I use the below SQL:
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
...
- 12.3k
1087
votes
20
answers
2.2m
views
How do I get list of all tables in a database using TSQL?
What is the best way to get the names of all of the tables in a specific database on SQL Server?
- 190k
1040
votes
24
answers
3.0m
views
How can I get column names from a table in SQL Server?
I want to query the name of all columns of a table. I found how to do this in:
Oracle
MySQL
PostgreSQL
But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
- 26k
1008
votes
25
answers
1.7m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18.5k
993
votes
19
answers
786k
views
Function vs. Stored Procedure in SQL Server
When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
- 80.7k
983
votes
32
answers
971k
views
How can I list all foreign keys referencing a given table in SQL Server?
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?
(SQL answers preferable ...
- 25.2k
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
944
votes
10
answers
847k
views
How to Join to first row
I'll use a concrete, but hypothetical, example.
Each Order normally has only one line item:
Orders:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-...
- 251k
924
votes
17
answers
760k
views
How can foreign key constraints be temporarily disabled using T-SQL?
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?
- 190k
894
votes
14
answers
1.2m
views
Should I use != or <> for not equal in T-SQL?
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?
I like !=, because <> reminds me of Visual Basic.
- 20.5k
890
votes
17
answers
660k
views
DateTime2 vs DateTime in SQL Server
Which one:
datetime
datetime2
is the recommended way to store date and time in SQL Server 2008+?
I'm aware of differences in precision (and storage space probably), but ignoring those for now, is ...
- 10.6k
877
votes
13
answers
524k
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
873
votes
15
answers
1.7m
views
How to drop a table if it exists?
The table name is Scores.
Is it correct to do the following?
IF EXISTS(SELECT *
FROM dbo.Scores)
DROP TABLE dbo.Scores
- 9,455
862
votes
5
answers
433k
views
Multiple Indexes vs Multi-Column Indexes
What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?
Are there reasons why one should be used over the other?
For example:
Create ...
- 74.9k
812
votes
13
answers
1.1m
views
SQL update query using joins
I have to update a field with a value which is returned by a join of 3 tables.
Example:
select
im.itemid
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
,mm....
- 216k
801
votes
11
answers
1.5m
views
Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
- 9,492
795
votes
16
answers
2.0m
views
Check if a temporary table exists and delete if it exists before creating a temporary table
I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later,...
- 8,994
784
votes
15
answers
361k
views
How do I create a unique constraint that also allows nulls?
I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null ...
- 11.9k
775
votes
19
answers
994k
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.6k
765
votes
24
answers
784k
views
What is the best way to auto-generate INSERT statements for a SQL Server table?
We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.
Every so often, we want ...
- 57.9k
751
votes
15
answers
1.5m
views
How to select all records from one table that do not exist in another table?
table1 (id, name)
table2 (id, name)
Query:
SELECT name
FROM table2
-- that are not in table1 already
- 17.4k
749
votes
5
answers
709k
views
C# Equivalent of SQL Server DataTypes
For the following SQL Server datatypes, what would be the corresponding datatype in C#?
Exact Numerics
bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money
Approximate Numerics
float
...
- 57.6k
738
votes
22
answers
927k
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
717
votes
12
answers
711k
views
What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
What is meant by nvarchar?
What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
- 44k
697
votes
11
answers
1.5m
views
How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same ...
- 33.2k
690
votes
16
answers
821k
views
What is "with (nolock)" in SQL Server?
Can someone explain the implications of using with (nolock) on queries, when you should/shouldn't use it?
For example, if you have a banking application with high transaction rates and a lot of data ...
- 87.3k