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
Filter by
Sorted by
Tagged with
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 ...
jamesmhaley's user avatar
  • 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?
Mathias's user avatar
  • 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?
JohnnyM's user avatar
  • 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 ...
Maciej's user avatar
  • 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?
KG Sosa's user avatar
  • 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 ("...
user avatar
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 ...
Ferdeen's user avatar
  • 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
Eric Labashosky's user avatar
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%'
gruber's user avatar
  • 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 ...
nettoon493's user avatar
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?
stimms's user avatar
  • 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 ...
Eric's user avatar
  • 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 ...
Ant Swift's user avatar
  • 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 ...
driis's user avatar
  • 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 ...
Vincent's user avatar
  • 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. ...
Seibar's user avatar
  • 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 ...
tim_wonil's user avatar
  • 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 ...
Karmic Coder's user avatar
  • 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 ...
Oded's user avatar
  • 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 ...
P.K's user avatar
  • 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 ...
user avatar
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] ...
user avatar
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. ...
Jeff Meatball Yang's user avatar
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 ...
DharaPPatel's user avatar
  • 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?
Ray's user avatar
  • 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)?
odiseh's user avatar
  • 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 ...
xorpower's user avatar
  • 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?
Tarik's user avatar
  • 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 ...
chillitom's user avatar
  • 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 ...
Manjot's user avatar
  • 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-...
Ian Boyd's user avatar
  • 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?
Ray's user avatar
  • 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.
Bob The Janitor's user avatar
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 ...
Mikeon's user avatar
  • 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 ...
super9's user avatar
  • 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
tmaster's user avatar
  • 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 ...
GateKiller's user avatar
  • 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....
Shyju's user avatar
  • 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.
Serhio g. Lazin's user avatar
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,...
Sridhar's user avatar
  • 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 ...
Stuart's user avatar
  • 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 ...
kazinix's user avatar
  • 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 ...
JosephStyons's user avatar
  • 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
z-boss's user avatar
  • 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 ...
George Stocker's user avatar
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 ...
Chris Cudmore's user avatar
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?
MrDatabase's user avatar
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 ...
Mark Struzinski's user avatar
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 ...
Andy White's user avatar
  • 87.3k

1
2 3 4 5
6692