Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit 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.

50
votes
1answer
5k views

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure ...
49
votes
4answers
269k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
48
votes
3answers
82k views

How do I get back some deleted records?

I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side. Is there any way to get back these ...
48
votes
3answers
30k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
47
votes
12answers
27k views

Why aren't primary key / foreign key matches used for joins?

As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
47
votes
4answers
32k views

Clustering vs. transactional replication vs. availability groups

Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails. As a developer and not a DBA, I ...
47
votes
2answers
71k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
47
votes
5answers
32k views

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why ...
47
votes
7answers
36k views

Why should I create an ID column when I can use others as key fields? [duplicate]

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think ...
47
votes
2answers
39k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
47
votes
2answers
49k views

Is there any difference at all between NUMERIC and DECIMAL?

I know that the NUMERIC and DECIMAL data types in SQL Server work the same: the syntax for creating them is the same, the ranges of values you can store in them is the same, etc. However, the MSDN ...
46
votes
8answers
101k views

How do I shrink all files quickly for all databases?

In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, ...
46
votes
2answers
6k views

Cast to date is sargable but is it a good idea?

In SQL Server 2008 the date datatype was added. Casting a datetime column to date is sargable and can use an index on the datetime column. select * from T where cast(DateTimeCol as date) = '20130101'...
46
votes
3answers
35k views

What is the difference between a connection and a session?

What is the difference between a connection and a session and how they are related?
46
votes
6answers
47k views

How to combine date and time to datetime2 in SQL Server?

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 ...
45
votes
4answers
37k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
45
votes
2answers
53k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
45
votes
3answers
10k views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
44
votes
4answers
101k views

Can I install SQL Server Management Studio locally?

Can I install SQL Server Management Studio on my desktop to access a database on a SQL Server 2012 instance? If so, where can I find the installer? Google searches only return 'express' versions ...
44
votes
6answers
82k views

After moving database (backup, restore), I have to re-add user

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio. Whenever I do this, the ...
44
votes
6answers
60k views

.bak file not visible in any directory in SSMS

I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it. I ...
43
votes
6answers
7k views

Is there any limit to the number of databases you can put on one SQL server?

I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too ...
43
votes
5answers
76k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
42
votes
2answers
35k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
42
votes
5answers
61k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
41
votes
8answers
12k views

Is select * still a big no-no on SQL Server 2012?

Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit. Is this still the case in later versions of SQL ...
41
votes
4answers
27k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
41
votes
3answers
3k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
41
votes
3answers
55k views

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM ...
41
votes
5answers
24k views

Data obfuscation in SQL Server

What is the best practice for Data Obfuscation in SQL Server? We'd like to use masked Production data in our UAT system. If we want to do it quickly, and with a higher level of obsfucation, what ...
40
votes
3answers
90k views

How can I see the current Database Mail configuration?

Our SQL Server (2008) instance is configured to send mail and everything is working correctly, but I can't figure out how to view the existing configuration, in particular the SMTP server. From SSMS ...
40
votes
4answers
92k views

SQL Server commands to clear caches before running a performance comparison

When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second. In a ...
40
votes
2answers
20k views

What does a DBA have to know about SSAS?

I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management. From the point of view of administering an instance ...
40
votes
5answers
70k views

SQL Server “Suspect” database?

What do you do when you have a database that is marked as Suspect? Restore from the last backup? Please advise.
40
votes
2answers
86k views

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old ...
40
votes
2answers
1k views

Why does changing the declared join column order introduce a sort?

I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique. The test setup Setup script, including some ...
39
votes
6answers
7k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
39
votes
3answers
2k views

Methods for finding new Trace Flags in SQL Server

There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, ...
39
votes
5answers
264k views

I can't connect to my servers SQL database via an IP Address

I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed. I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS. However ...
39
votes
5answers
41k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
39
votes
4answers
92k views

How export a sql server 2008 diagram to PDF filetype?

I want to have an export from my database diagram to PDF or image types. How can I do this? I worked with SQL Server 2008 R2.
39
votes
3answers
191k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
39
votes
3answers
5k views

Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
39
votes
6answers
11k views

Why do sequential GUID keys perform faster than sequential INT keys in my test case?

After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid()...
39
votes
4answers
89k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
39
votes
2answers
18k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
39
votes
1answer
44k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
38
votes
10answers
43k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
38
votes
3answers
189k views

How do you grant execute permission for a single stored procedure?

Normally when I create a stored procedure I use the following as a template of sort Create procedure <procedurename> <@param1 <type>, @param2 <type>, etc..> as begin <...
38
votes
4answers
41k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post https://stackoverflow.com/questions/1307990/...