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.
33,665
questions
8
votes
2
answers
1k
views
Is this a new common pattern in Oracle Where exists ( Select NULL FROM...)?
Years ago, it was common to write
where exists (Select * from some_table where some_condition)
Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star
where ...
- 12.2k
15
votes
5
answers
16k
views
Does it make sense to use SQL Server's bracket notation in hand written code?
Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.
When I first saw it, I though wow a reincarnation of the somewhat ...
- 12.2k
172
votes
6
answers
165k
views
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 ...
- 3,433
25
votes
3
answers
5k
views
Which collation should I choose for a muiti-language website?
Does a collation have any influence over a query speed? Does the size of a table change depending of the collation?
If I want to build a website that must support all possible languages (lets take ...
- 3,433
13
votes
1
answer
1k
views
SQL Server - How are data pages stored when using a clustered index
I recently heard that the data pages in a clustered index aren't stored contiguously. Is this true?
Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I ...
- 233
24
votes
2
answers
3k
views
Do I need separate indexes for each type of query, or will one multi-column index work?
I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic.
My basic understanding is that generally speaking, a single index that ...
- 397
274
votes
7
answers
147k
views
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 ...
- 12.2k
58
votes
6
answers
78k
views
What are the main causes of deadlocks and can they be prevented?
Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was ...
- 4,602
129
votes
7
answers
150k
views
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 ...
- 1,393
6
votes
2
answers
2k
views
Dropping duplicate|redundant Unique Constraint from FILESTREAM table
I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie:
ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT [...
- 143
3
votes
1
answer
1k
views
SQL 2005: logical consistency-based I/O error: incorrect checksum
I got this error this morning during the execution of a SSIS package:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5fb84eb5; actual: 0x7fb84eb5). It ...
- 133
39
votes
2
answers
129k
views
How do you stop, take offline, and delete a SQL Server database in recovery?
I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.
I need to either stop the recovery, or kill it in some way.
I don't care ...
CrapHands
5
votes
5
answers
24k
views
How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?
How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?
- 149
2
votes
1
answer
3k
views
SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist
SQL Server 2005 sp3 Standard on 64-bit Windows Server 2008
inconsistently receiving an error that seems to indicate corruption
select *
from some_table
where text_field like ‘%the fox did not go ...
- 263
0
votes
1
answer
150
views
SQL Server Point in Time Restore Scenario
Hopefully you can help out on this as I may be up a creek!
I am working with a database using the FULL recovery model. The database has had nightly database backups but their transaction log has ...
retailevolved
8
votes
4
answers
12k
views
When is a full table scan better than index scan?
What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?
ahsan
4
votes
1
answer
8k
views
Sql FILESTREAM garbage collection issue
Updated 3x
I'm using FILESTREAM, but am having an issue with garbage collection.
The FILESTREAM files are not being cleaned up when I set the column to null.
In the event log, there is an enormous ...
- 141
5
votes
1
answer
911
views
Query Execution Time
I am querying a SQL server 2008 database table having more than 500 million records.
The query I fired to move around 10 million records to temp table took 9 hours.
Similar query I fired which has ...
user219628
2
votes
2
answers
363
views
What is wrong in debunking myths about clustered indexes?
What is wrong in code. or its results, illustrating that clustered index is evil [1]?
and how to de-debunk, i.e. to return to customary myths and best practices?
[1]
Debunking myths about ...
9
votes
2
answers
1k
views
Clustered indexing is now must - why?
Earlier, there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes.
Well, I understood that they are to be used sometimes with proper + specific ...
2
votes
1
answer
591
views
SQL Server Assertion Error
Does anyone have a solution to this problem?
SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'. This error may be ...
- 633
4
votes
2
answers
719
views
Bizarre crash in T-SQL - what is it?
What would be a typical situation under which the following error would occur, and what does it mean?
Also note that I find myself disconnected from the server when the query fails (but I have not ...
Alexander
1
vote
2
answers
5k
views
Repair a SQL Server 2000 MDF file
I had a SQL Server 2000 MDF on a SQL Server 2005 server (moved from prior version), there was a hardware failure.
After sending harddisk, it was supposed the mdf / ldf files were recovered, but I am ...
eric
6
votes
2
answers
680
views
SQL Filegroup degrading performance
On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.
On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was ...
- 161
5
votes
2
answers
11k
views
SQL Server 2008 DBCC Problems
We have a database Ms
DBCC CHECKDB (MS)
Or
ALTER DATABASE MS SET SINGLE_USER
DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MS SET MULTI_USER
Error message
Msg 0, Level 11, State 0, Line 0: ...
- 151
8
votes
3
answers
112
views
Does SQL Server guarantee accurate data storage? If so, how?
This question is specific to SQL Server, but other DB replies welcome.
Read below for some history. My short question is, how does SQL Server guarantee that when I request "ABCDE" to be written to ...
logo1664
5
votes
3
answers
192
views
Interpreting an execution plan
I need some help analysing some simple t-sql execution plans.
I have a table [User] with the following 3 columns:
Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))
I have Created an ...
Gnomo
41
votes
3
answers
74k
views
What is the difference between Shrink Database and File?
DBCC ShrinkDatabase()
DBCC ShrinkFile()
Do I need to run both DBCC commands in order to shrink the database?
What is the difference between these two above?
Jango
10
votes
10
answers
21k
views
Shrink a database below its initial size
I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% ...
Amjid Qureshi
2
votes
1
answer
3k
views
Exclude .LOG file while restoring a database
I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but ...
stackoverflowuser
14
votes
3
answers
12k
views
SQL Server - Temporary vs. Physical Tables
A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, ...
- 298
12
votes
3
answers
23k
views
Performance hit using CAST in T-SQL
We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField).
If myField is of type NVARCHAR, we can ...
Ryan Delucchi
4
votes
1
answer
1k
views
DBCC CHECKDB WITH DATA_PURITY gives out of range error
I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error:
Msg 2570, Level 16, State 3, Line 2
Page (1:19558), slot 13 in object ...
- 515
2
votes
1
answer
404
views
SQL Server DB Restore Clustered to Single Node?
Can you restore a DB from a SQL Server 2005 Clustered Environment (Prod) to a single server (single node) (SQL Server 2005) in Dev/Sys?
Cheers,
Conor
Conor
7
votes
3
answers
10k
views
Howto use Windows Authentication with SQL Server 2008 Express on a workgroup network?
I have two computers running SQL Server 2008 Express: c01 and c02, I setup both for remote connection using windows authentication. Worked fine for c02 but not for c01.
This is the error message I'm ...
- 171
22
votes
5
answers
9k
views
Using IF in T-SQL weakens or breaks execution plan caching?
It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
- 321
5
votes
3
answers
5k
views
Should I be regularly shrinking my DB or at least my log file?
My question is, should I be running one or both of the shrink command regularly,
DBCC SHRINKDATABASE
OR
DBCC SHRINKFILE
=============================
background
Sql Server: Database is 200 ...
Tom DeMille
4
votes
3
answers
547
views
Shrink database after removing extra data [duplicate]
We have a need to fit a database in 4GB in order to use SQL Server Express Edition. I started from a 7GB database, and found a lot of unneeded records, and deleted them. After shrinking, the database ...
- 166
7
votes
1
answer
642
views
Is there a performance hit by breaking out tables into different schemas in SQL Server?
Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server.
For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
pms1969
4
votes
3
answers
2k
views
Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?
I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration?
Can I run a command that basically says, "use OPTION(RECOMPILE) for all ...
kamens
3
votes
3
answers
6k
views
Undo Database Restore in SQL Server 2008
I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?
Vivek Chandraprakash
4
votes
2
answers
251
views
Database Mirroring of SQL server
I have two databases that are mirrored to another server using database mirroring.
The mirror server has to be down for some reason for few days.
Now, the production server is having principal ...
user186246
5
votes
1
answer
61
views
How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?
How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?
For instance:
The restore process will create:
bob.mdf
bob.ldf
etc...
- 263
12
votes
3
answers
553
views
Which is faster join
Which is faster
SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique
or
SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID
- 229
94
votes
6
answers
91k
views
Why would SET ARITHABORT ON dramatically speed up a query?
The query is a single select containing a lot of grouping levels and aggragate operations.
With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this ...
- 3,572
4
votes
2
answers
111
views
query plan caching (sql server)
Is there a measure of time frame as to which the execution plan of inline queries will cached?
- 153
25
votes
4
answers
102k
views
Grant permissions to run a SQL Server job
I have a job on my SQL Server 2005 instance that I want to allow any database user to run.
I'm not worried about security, since the input to the job's actual work comes from a database table. Just ...
- 403
0
votes
1
answer
2k
views
Sql Server merge replication primary key violations
Using Sql Server 2008. Pretend I have a publisher and 2 subscribers A and B. The replicated table has a guid on it and also a primary key that is not the guid. Frequently, updates happen at both ...
Gary
3
votes
4
answers
557
views
Help improving SQL Server 2005 query execution time
One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster.
I am looking at the execution plan and two things are contributing most of the cost. ...
- 2,403
8
votes
8
answers
2k
views
Granting SA privileges for Developers on the development box
In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still ...
- 710