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)

8
votes
3answers
236 views

Nonclustered Index Insert

Say I have a table like this: create table SomeTable ( id int identity(1, 1) not null primary key clustered, SomeString1 varchar(50) not null, SomeString2 varchar(50) not null ) go ...
7
votes
1answer
3k views

SQL Server Deadlock Graph - Table, Page or Row Lock?

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
6
votes
1answer
11k views

Finding last time table was updated?

The query: SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate will tell me the last time a table was created and modified (from a ...
6
votes
4answers
16k views

SQL performance issues with remote query across linked server

This sproc create proc dbo.Get_Accounts as begin declare @current_date datetime set @current_date = dbo.fn_currdate() select [fields] into dbo.current_accounts from ...
5
votes
3answers
144 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
5
votes
2answers
3k views

SQL Server - Tempdb data/log file placement

Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive. On monitoring we have found I/O issues on TempDB. We got an extra drive available - should we move the TempDB Log ...
5
votes
6answers
3k views

Recommendations for MS SQL Server monitoring software or service? [closed]

I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database ...
4
votes
3answers
440 views

Why does my procedure run slowly until I update statistics?

I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
4
votes
2answers
7k 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 ...
0
votes
2answers
2k views

help in multi-language medical database?

My project is to design a medical database for many countries. This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language. I started my ...
33
votes
5answers
13k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with ...
10
votes
1answer
575 views

SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup: A 2-server SQL 2012 SP1 ...
10
votes
2answers
4k views

Difference between SQL batch, statement and RPC?

What is the difference between SQL batch, T-SQL statement and Remote Procedure Call? How can I tell if part of the T-SQL code is a batch or statement?
9
votes
3answers
430 views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
9
votes
2answers
621 views

Hard limitation for merge replication article count?

A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
8
votes
3answers
2k views

INSERT takes over 5 hours, when inserting more than specific number of rows

When inserting less then about 1,350,000 rows to the table it all takes about 2 minutes, however when number of inserted rows is bigger, then the time needed to insert data grows to about 5 hours. ...
8
votes
1answer
405 views

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation "... All the earlier versions of a particular record are chained in a linked ...
8
votes
2answers
1k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
8
votes
2answers
4k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
8
votes
2answers
3k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
8
votes
2answers
7k views

Difference Between Row Level and Page Level Locking and Consequences

When attempting to run my Maintenance Plan, I receive the following error: Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized ...
8
votes
1answer
665 views

Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
8
votes
2answers
794 views

What is the meaning of DOP in the context of SQL Server?

What is the meaning of DOP in the context of sql server?
7
votes
4answers
8k views

Converting RTF in a text column to plain text in bulk

I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need ...
7
votes
2answers
611 views

Can Availability Groups replace transactional replication?

I've been looking at the new features of SQL Server 2012, and how it can help my current situation. Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single ...
7
votes
2answers
552 views

Code creates different plan when ran ad-hoc vs. in a stored procedure

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc. I have rebuilt all the indexes for the tables used by the ...
7
votes
4answers
1k views

SQL Server: what should be done when a process is blocking another process?

I'm an accidental DBA and still learning. When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done? Should I simply kill those processes or is ...
6
votes
4answers
6k 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 ...
6
votes
2answers
2k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
6
votes
3answers
9k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
6
votes
2answers
14k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
6
votes
1answer
245 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
3answers
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
5answers
6k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
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
710 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 ...
6
votes
3answers
6k views

Why is a .bak so much smaller than the database it's a backup of?

I just took a backup of a SQL Server database. The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller. My first guess when one version of a set of ...
4
votes
1answer
415 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
218 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
6k 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
936 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
1k 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
406 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
124 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
5k 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
130 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 ...