Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Microsoft's SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality. It originated from the Sybase SQL Server 4.x codebase and Transact-SQL dialect (T-SQL), but it has forked significantly since then.

SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. The latest released version is SQL Server 2017.

The SQL Server product range is split broadly into six categories:

  1. SQL Server is the main suite of enterprise and developer server products. Primary differences are licensing costs, capacities, and components included in the product, with some minor differences supported language features. Standard components include database language and storage server, developer tools, ETL tools, schedulers, and replication. Other components include OLAP, reporting, and parallel computation. Components runs as NT Services.

  2. SQL Server Express is free for use and distribution but has reduced engine performance, functionality and capacity than found in its other server siblings. It is focused on small deployments and runs as an NT Service.

  3. SQL Server Compact Edition is an embeddable subset of SQL Server. Like the Express edition it has a reduced language, functionality and capacity, but it is free to distribute. It's focused on small installations and desktop applications where its small footprint and no-management-required features are a great advantage.

Note: SQL Server Compact Edition is deprecated. Customers should use SQL Server Express (and possibly LocalDB).

  1. Azure SQL Database is a completely managed, hosted, high-availability SQL Server Database with some language syntax support for federated queries, operated in Microsoft Azure datacenters.

  2. Azure SQL Managed Instance is a completely managed, hosted, high-availability instance of SQL Server with some language syntax support for federated queries, operated in Microsoft Azure datacenters.

  3. SQL Server Analytics Platform System (or APS), formerly known as SQL Server Parallel Data Warehouse (PDW), is a pre-built data warehouse appliance that offers massively parallel processing for SQL Server, allowing support for many hundreds of terabytes.

  4. Azure SQL Data Warehouse is an enterprise-class distributed database in the Azure Cloud capable of processing up to petabyte volumes of relational and non-relational data. It is the industry's first cloud data warehouse with grow, shrink, and pause in seconds.

SQL Server Release History

Version      Year  Release Name               Codename
1.0 (OS/2)   1989  SQL Server 1.0 (16 bit)    Ashton-Tate 
1.1 (OS/2)   1991  SQL Server 1.1 (16 bit)    -
4.21(WinNT)  1993  SQL Server 4.21            SQLNT
6.0          1995  SQL Server 6.0             SQL95
6.5          1996  SQL Server 6.5             Hydra
7.0          1998  SQL Server 7.0             Sphinx
 -           1999  SQL Server 7.0 OLAP Tools  Plato
8.0          2000  SQL Server 2000            Shiloh
8.0          2003  SQL Server 2000 x64        Liberty
9.0          2005  SQL Server 2005            Yukon
10.0         2008  SQL Server 2008            Katmai
10.25        2010  SQL Azure DB               CloudDatabase
10.5         2010  SQL Server 2008 R2         Kilimanjaro (aka KJ)
11.0         2012  SQL Server 2012            Denali
12.0         2014  SQL Server 2014            Hekaton
13.0         2016  SQL Server 2016            SQL16 
14.0         2017  SQL Server 2017            Helsinki 
15.0         2019  SQL Server 2019            Seattle

References

Tagging recommendation:

There are several version- and edition-specific tags. It is recommended to use the tag together with the version- and/or edition-specific tag; for example, or . Do not use this tag for other types of DBMS (, , etc.).

32940 questions
480
votes
2 answers

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
Martin Smith
  • 80,333
  • 15
  • 230
  • 323
297
votes
4 answers

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log grows so large? Why is my log file so big? What…
Mike Walsh
  • 18,013
  • 6
  • 46
  • 72
266
votes
7 answers

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id int primary key, code varchar(10) NULL ); go create…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
243
votes
18 answers

How do you document your databases?

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using. How do you document your database? (SQL-Server)…
user316
  • 961
  • 3
  • 7
  • 4
202
votes
5 answers

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key? Thanks in advance
Stuart Blackler
  • 4,462
  • 7
  • 27
  • 42
186
votes
7 answers

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, Column3 FROM SomeTable ) SELECT * FROM…
Rachel
  • 8,367
  • 20
  • 48
  • 74
174
votes
19 answers

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production…
Tom Hunter
  • 2,139
  • 3
  • 16
  • 11
172
votes
4 answers

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

Any suggestions on how to deal with this error: TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit code 1638. For help, click:…
Jonathan Allen
  • 3,526
  • 6
  • 23
  • 24
163
votes
6 answers

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and…
BrunoLM
  • 3,313
  • 7
  • 26
  • 22
154
votes
12 answers

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
user2645263
127
votes
7 answers

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that…
misterjaytee
  • 1,373
  • 3
  • 11
  • 8
119
votes
4 answers

Why are numbers tables "invaluable"?

Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table: USE Model GO CREATE TABLE Numbers ( Number INT NOT NULL, CONSTRAINT PK_Numbers PRIMARY KEY…
Jeff Atwood
  • 2,354
  • 2
  • 21
  • 15
118
votes
3 answers

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it is not really necessary? Even if you are using an…
elranu
  • 1,283
  • 2
  • 9
  • 7
112
votes
6 answers

SQL Server Management Studio 18 won't open (only splash screen pops up)

I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits. Running ssms with the -log parameter reveals an error message: CreateInstance failed for…
Mitch
  • 2,618
  • 2
  • 17
  • 23
106
votes
7 answers

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost all of whom are much more experienced than I am)…
user606723
  • 1,506
  • 4
  • 14
  • 16
1
2 3
…
99 100