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

learn more… | top users | synonyms (2)

7
votes
6answers
3k 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 ...
6
votes
2answers
363 views

Filter table before join

I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called ...
6
votes
2answers
1k views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
6
votes
1answer
499 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 ...
6
votes
1answer
5k views

Restore encrypted database to another server

I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. ...
5
votes
2answers
363 views

When was a Database taken Offline

I am trying to find a way to figure out when was couple of my sql databases were taken offline. I checked the the logs but could not find any such info and moreover there is no default trace enabled. ...
5
votes
2answers
7k views

SAN block size for SQL Server on 4KB NTFS

A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's ...
5
votes
1answer
6k views

Give permission to select from system tables

I have a database Microsoft SQL Server 2008 r2. A user called marie is defined, who can access tables on the main database; this works well. Now, I would like marie to be able to perform this query: ...
4
votes
1answer
123 views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL db. If the row already exists in the table, we update that ...
4
votes
1answer
105 views

Proving the usefulness of a specific index

I'm looking for a way to prove that some recently added indexes are positively impacting a production system. I've collected some dmv statistics to show that the indexes are indeed being used ...
4
votes
1answer
795 views

CREATE DATABASE permission denied for sysadmin

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User ...
4
votes
1answer
3k views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
2answers
8k 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 ...
4
votes
3answers
3k views

Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, ...
3
votes
1answer
508 views

Running functions in parallel

This is for SQL Server 2012. We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
3
votes
2answers
1k views

Can I optimize this MERGE statement?

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows. In my development environment, the query ...
3
votes
2answers
527 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
2k 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, ...
2
votes
1answer
2k views

Transactional replication altering tables and adding stored procedures

I have a database that is replicated in several locations. It is setup as transactional replication. However, now I need to add more columns to the table. Add more stored procedures and etc. Hence, if ...
2
votes
3answers
1k 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
2answers
3k views

SQL Server replication subscriptions marked as inactive

Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever? It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription ...
2
votes
1answer
475 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
3k 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 ...
1
vote
2answers
813 views

“In Use” memory on a SQL Server box

I have a SQL Server 2012 database server with 64GB of RAM. A process called DataPumpService runs on the same box and loads around 400 million records from 2700 flat files. I want to understand what ...
0
votes
3answers
496 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
15
votes
2answers
1k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
15
votes
4answers
10k 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 ...
14
votes
1answer
2k 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 ...
14
votes
1answer
7k views

How do I read Query Cost, and is it always a percentage?

I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric. According to any documentation I could find via ...
13
votes
3answers
3k 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 ...
13
votes
3answers
3k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
12
votes
3answers
10k views

Common Table Expression (CTE) benefits?

From msdn : Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. I'm using CTEs quite a lot, but I've never thought deeply about the ...
11
votes
1answer
295 views

Why does subquery use parallelism and join doesn't?

Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete. Join ...
10
votes
3answers
829 views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
10
votes
4answers
3k views

Sort order specified in primary key, yet sorting is executed on SELECT

I'm storing sensor data in a table SensorValues. The table and primary key is as follows: CREATE TABLE [dbo].[SensorValues]( [DeviceId] [int] NOT NULL, [SensorId] [int] NOT NULL, [SensorValue] ...
10
votes
3answers
4k 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, ...
10
votes
5answers
4k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
9
votes
1answer
965 views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
9
votes
2answers
6k views

Need to understand parallel query execution error

Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources ...
9
votes
1answer
300 views

DB_ID context from farther up call stack

In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack? My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database ...
9
votes
3answers
1k views

How to control the order of servers listed in the SSMS connection dialog?

My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat. One minor annoyance is the connection dialog in SSMS re-shuffling the server ...
9
votes
2answers
2k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
9
votes
1answer
2k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
9
votes
3answers
368 views

Are heaps considered an index structure or are they strictly a table structure without index?

Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505 Heaps: Are they considered an index structure or are they strictly a table structure without index?
9
votes
5answers
3k views

When should nonclustered indexes be stored on separate filegroups?

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
9
votes
2answers
756 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
8
votes
1answer
269 views

Most efficient way to get stats on an entire SQL Server database

What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented. Is there a particular script I can run ...
8
votes
1answer
598 views

Generating large strings for test data

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
8
votes
5answers
3k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
8
votes
3answers
827 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...