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)

6
votes
1answer
250 views

How to Optimise Query

I have a database structure similar to this, CREATE TABLE [dbo].[Dispatch]( [DispatchId] [int] NOT NULL, [ContractId] [int] NOT NULL, [DispatchDescription] [nvarchar](50) NOT NULL, ...
6
votes
2answers
3k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
6
votes
4answers
3k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
6
votes
2answers
4k views

Recover accidentally deleted rows from a table

My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup. Is it possible to restore the deleted ...
6
votes
1answer
725 views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
5
votes
1answer
720 views

Allow non-sys admin, non-owner of a job to execute it

I have a job that runs an SSIS package. Currently the owner is a proxy account. I can run the job manually from a sys-admin account. Our web service logs in using a limited account. It needs to ...
4
votes
1answer
314 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
4
votes
6answers
7k views

SQL Server log shrinking Issue [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to ...
4
votes
3answers
3k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
4
votes
1answer
979 views

SQL Server: How to create a table that fills one 8 KB page?

Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.
3
votes
1answer
3k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
3
votes
1answer
2k views

SQL Server Many-to-One replication

I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data. I would like to establish a reporting ...
3
votes
2answers
469 views

Server permissions of an activation stored procedure of a Server Broker queue

I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on ...
3
votes
2answers
1k views

SQL Server media belongs to which edition

Could anyone please help me on below question? Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to which edition (like developer, standard or ...
3
votes
0answers
126 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
3
votes
1answer
7k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
3
votes
1answer
132 views

Analysing A Query Plan

Why is the following query slow? select count(*) from [dbo].[mt_dispatch_link] , [dbo].[_mt_dispatch] [_mt_dispatch] where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id ...
3
votes
3answers
2k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
3
votes
2answers
3k views

Problem Reaching VM SQL Server Instance

Here's what I'm working with right now. I have a host OS which includes a SQL Server 2008 Enterprise instance. I have a virtual machine running on this host which has a SQL Server 2008 R2 Express ...
3
votes
2answers
8k views

SQL query that concatenate values from duplicate rows in a single table

Lets say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with this table of duplicated rows. I will fix it ...
2
votes
1answer
94 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...
2
votes
1answer
3k views

Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
2
votes
2answers
1k views

Move (Restore) SQL Server 2005 database which does not exist before

I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do is create a .bak file ...
1
vote
3answers
192 views

Undo SQL UPDATE Azure DB

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
1
vote
2answers
241 views

SQL 2008 Server - performance loss possibly connected with a very large table

Migrated from http://serverfault.com/questions/615129/sql-2008-server-performance-loss-possibly-connected-with-a-very-large-table We have SQL Server 2008 with 3 active databases running on it. DB1 ...
1
vote
2answers
8k views

How do I find a long running query with process ID, process name , login time, user , start time and duration?

Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration. I am looking for a ...
1
vote
3answers
323 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
1
vote
1answer
4k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
22
votes
3answers
5k 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 ...
14
votes
2answers
573 views

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
11
votes
5answers
3k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
11
votes
2answers
1k views

INT or CHAR for a Type Field

What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema: create table Car ( Name varchar(100) not null, Description varchar(100) not ...
9
votes
4answers
3k views

When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option. I believe Checksum was introduced in SQL Server 2005 ...
9
votes
3answers
3k views

Restrict access for Windows administrator

As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows ...
9
votes
2answers
2k views

Unique Constraints on Nullable Columns in SQL Server 2005

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
8
votes
1answer
4k views

SQL Server nvarchar(max) vs nvarchar(n) affects performance

This is SQL Server 2008 R2 SP2. I have 2 tables. Both are identical (data and indexing), except the first table has a VALUE column as nvarchar(max) and the second has the same column as ...
8
votes
1answer
6k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
8
votes
2answers
4k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
3answers
6k views

How to free SQL Server memory?

I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When ...
8
votes
7answers
821 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
7
votes
4answers
2k views

Does SQL Server ever delete old error log files?

I know I can cycle the current error log easily by running sp_cycle_errorlog, but I'm wondering if SQL Server will ever delete the old/archived error log files at all. Can't seem to find an answer to ...
7
votes
1answer
1k views

Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?

My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application updates on an ...
7
votes
1answer
929 views

Why does the order of parameters matter for sp_trace_create?

The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options: ...
7
votes
2answers
873 views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
6
votes
1answer
107 views

Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based ...
6
votes
2answers
3k views

When to make changes to the cost threshold for parallelism

While examining a performance issue , I have seen an influx on CXPACKETS suggesting I might need to look at the cost threshold for parallelism and perhaps the MAXDOP. Before making any drastic ...
6
votes
3answers
18k views

SQL Server Restore a SINGLE table from a backup [duplicate]

on SQL Server 2005, we do a weekly full backup with nightly incremental backups. I want to know if it is possible to restore a single table from a backup, either to the source database or a ...
6
votes
2answers
1k views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
6
votes
2answers
285 views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
6
votes
4answers
5k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...