Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
21
votes
7answers
1k views
What is the difference between a “record” and a “row” in SQL Server?
There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.
So how do we differentiate between these related terms and how we ...
17
votes
4answers
3k views
SQL Server “empty table” is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
14
votes
7answers
2k views
Why is it a bad practice to allow everyone to use the sa login?
Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.
I've read that it's a best practice to not let users use the sa login directly, instead using ...
13
votes
3answers
2k views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
28
votes
3answers
9k 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 ...
16
votes
4answers
861 views
Does the order of columns in a table's definition matter?
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
15
votes
3answers
3k views
Which PowerShell technique should I use to talk to SQL Server?
I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that ...
14
votes
3answers
893 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
13
votes
3answers
1k views
Managing concurrency when using SELECT-UPDATE pattern
Let's say you have the following code (please ignore that it's awful):
BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, ...
12
votes
4answers
6k 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 ...
9
votes
7answers
667 views
Version control for database objects
The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
13
votes
3answers
2k views
Running total with count?
As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count:
sum(count (distinct (customers)))
Say if I ran the count ...
11
votes
3answers
824 views
How often to backup the master database?
The suggestion in BOL is fairly vague:
Back up master as often as necessary to protect the data sufficiently
for your business needs. We recommend a regular backup schedule, which
you can ...
9
votes
1answer
628 views
SQL Server Cache Flush and Disk I/O
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
9
votes
2answers
566 views
Why are queries parsed in such a way that disallows the use of column aliases in most clauses?
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.
The MSDN docs say that the general logical ...
7
votes
4answers
12k views
How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist
I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights).
There are no other ...
6
votes
2answers
1k views
SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available)
I have SQL Server 2012 Local DB Installed, and am connecting to it via (localdb)\v11.0.
When trying to restore a backup file, I get:
TITLE: Microsoft SQL Server Management Studio
Property ...
5
votes
3answers
9k views
How do I run a batch file from a scheduled Job via SQL Server Agent
I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I ...
12
votes
3answers
8k views
What risks are there if we enable read committed snapshot in sql-server?
I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?
eg.
Will this affect recovery of the database?
Is there anything ...
10
votes
2answers
252 views
Understanding MS SQL Server encryption and backups
So I was asking in chat about how secure SQL Server backup files are.
I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data.
So lets look at ...
10
votes
5answers
847 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 ...
9
votes
1answer
707 views
What's the anatomy of a columnstore index?
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.
I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
8
votes
3answers
981 views
SQL Server database synchronization
Problem definition
Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
8
votes
2answers
526 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
8
votes
4answers
4k views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
7
votes
5answers
9k views
How to efficiently check EXISTS on multiple columns?
This is an issue I come up against periodically and have not yet found a good solution for.
Supposing the following table structure
CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C ...
7
votes
1answer
371 views
alert when any job in a job category fails
Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?
I am wondering because I would like to set up an e-mail anytime an SSRS ...
6
votes
3answers
589 views
What is a good, repeatable way to calculate MAXDOP on SQL Server?
When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting:
/*
This will recommend a MAXDOP setting appropriate for your machine's ...
6
votes
1answer
372 views
Equality query on NVARCHAR column yields multiple results in SQL Server 2012
I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard).
I've noticed an interesting phenomenon when querying unicode words. Given the definition:
CREATE ...
6
votes
3answers
332 views
PostgreSQL Initial Database Size
There are 2 parts to my question.
Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?
...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
4answers
1k views
Slow temp table drops in sql 2005
I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers ...
6
votes
6answers
2k views
Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios
From what I can tell, there are three possible ways of backing up your SQL Server database
Full backup
Differential backup
Log Shipping
What are the pros and cons of each strategy and in what ...
5
votes
1answer
226 views
How much memory is a table taking up?
Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)?
For example, suppose I have a table with 30 million records. I'd like to know how many pages ...
5
votes
3answers
1k views
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
5
votes
3answers
2k views
Will the transaction log shrink automagically in SQL Server?
When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
3
votes
4answers
459 views
SQL Server - Separating data, log, and TempDB files on a SAN
I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
3
votes
2answers
278 views
Help with a multi-language medical database
My project is to design a database for medicines in many countries.
Medicines have various criteria like composition, price, manufacturer.
Database will be multi-language; related to each country.
...
3
votes
2answers
662 views
TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers
I've got an issue with SQL Server.
The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers.
This happens randomly, once or twice a week, ...
3
votes
2answers
4k views
SQL 2008 R2 creates user/schema when Windows user creates tables
We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:
USE master
go
CREATE LOGIN ...
2
votes
4answers
245 views
High CPU usage on SQL server - Slow queries [closed]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
2
votes
3answers
139 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
2
votes
1answer
189 views
Understanding SQL Server encryption (by password)?
After asking this I still have a question.
A company need to store sensitive data. Please let's not talk about certificate right now.
A DBA does the following :
CREATE SYMMETRIC KEY ...
2
votes
2answers
1k views
Restore Database script
I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
17
votes
5answers
6k views
SQL Server load balancing
What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased?
I'm trying to ...
14
votes
4answers
5k views
What is the best query to use to monitor a SQL Server database's status?
I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.
This is the ...
12
votes
1answer
443 views
Where are Statistics physically stored in SQL Server?
Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool?
More specifically, is there a way to figure out the pages used by ...
12
votes
3answers
4k views
How does shrinking a SQL Server log file affect performance?
I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
11
votes
3answers
828 views
Should I migrate data using detach/copy/attach or through backup-restore-replay?
I am about to embark on migrating database files to a new SAN (from an old SAN) abd I have a couple of options to implement this. (1) It was suggested that I look into the level of effort of restoring ...
