All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

6
votes
1answer
13k views

SQL Server Maximum and Minimum memory configuration

What are the correct settings for min-memory and max-memory in this use case? The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It'...
4
votes
2answers
2k views

Run a PowerShell script on a different server - from inside a SQL Server job

I have a procedure to backup SSAS Databases. That is working as a charm. Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days. to ...
2
votes
1answer
528 views

Need help with SQL Server PIVOT

I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations ...
5
votes
3answers
5k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
111
votes
8answers
90k views

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, ...
99
votes
6answers
33k 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 ...
17
votes
7answers
51k views

Need to migrate SQL Server to MySQL

I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
22
votes
1answer
42k views

How do I attach a database in SQL Server?

Some common questions relating to attaching databases in SQL Server: What does it mean to attach or detach a database? How do I detach a database? How do I attach a database? What ...
21
votes
4answers
10k views

Using SQL Profiler on a database that's in production

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems. But I've always used it on my development ...
9
votes
2answers
7k views

How to create an Event Notification that runs a Job/procedure when mirroring state changes

I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL? Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
6
votes
5answers
58k views

List all permissions for a given role?

I've searched around all over and haven't found a conclusive answer to this question. I need a script that can give ALL permissions for an associated role. Any thoughts, or is it even possible? ...
5
votes
1answer
5k views

Allow non-sysadmin, non-owner of a SQL Server Agent 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 ...
14
votes
2answers
13k 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 ...
6
votes
1answer
11k views

SQL Server - How to determine ideal memory for instance?

We have some virtual machines that have X memory allocated to them. This amount of memory is somewhat random. It was allocated to the machine because that was the amount of memory the physical ...
6
votes
1answer
2k views

Storage order vs Result order

This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT. @Catcall says this on the subject of storage order (clustered index) and the output order ...
34
votes
7answers
24k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
54
votes
4answers
34k 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 ...
44
votes
4answers
14k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
47
votes
3answers
30k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
45
votes
8answers
14k 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 ...
22
votes
3answers
53k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
18
votes
4answers
12k views

Determining how a schema change occurred?

Something bad happened yesterday. A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this ...
27
votes
2answers
6k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[...
21
votes
2answers
5k views

Handling growing number of Tenants in Multi-tenant Database Architecture

Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way ...
15
votes
5answers
18k views

Optimal drive configuration for SQL Server 2008R2

I have a fairly busy database server running SQL Server 2008 R2 that has the following setup: SATA RAID 1 (2 Drives) - OS / Programs SAS RAID 10 (4 Drives) - Sql Database Files (data and logs) SAS ...
22
votes
2answers
39k views

How to find the SQL statements that caused tempdb growth?

The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create ...
31
votes
6answers
4k views

The smallest backup possible … with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
8
votes
2answers
19k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
22
votes
5answers
5k views

Why use an int as a lookup table's primary key?

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string). I understand that using a ...
10
votes
1answer
46k views

Setting user permissions for different SQL Server schemas

I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo. I'm trying to do the following: dbo schema functions as it would normally, has ...
29
votes
2answers
2k views

Why are Denali sequences supposed to perform better than identity columns?

In his answer to Which is better: identity columns or generated uinique id values? mrdenny says: When SQL Denali comes out it will support sequences which will be more efficient than identity, but ...
22
votes
1answer
11k views

DELETE vs TRUNCATE

I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of: DELETE -> the database ...
16
votes
4answers
37k 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 ...
15
votes
4answers
52k views

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the ...
3
votes
1answer
5k 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 ...
9
votes
1answer
1k views

Is that a good idea to use one database for 50.000+ shops?

I know Shopify use only one database for all shops. But how can they handle their database with such a big data? Is that a good idea to use single database for 50.000+ shops?
15
votes
2answers
16k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
3
votes
1answer
468 views

VLDB with large quantity of data file imports

I am a little out of my league with this scenario and hope this community can help get me on the right direction. I have recently been tasked with re-designing and implementing a database application ...
20
votes
3answers
4k views

Justify NOT using (nolock) hint in every query

Have you ever had to justify NOT using a query hint? I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
7
votes
2answers
23k views

Quick look at how much RAM is allocated to SQL Server?

With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server. With SQL Server 2008, the Working Set or Commit Size never ...
4
votes
2answers
2k views

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the agent to see if it is running or not, and ...
5
votes
1answer
4k views

Find transactions that are filling up the version store

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases. Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
4
votes
2answers
16k views

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

Let's say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with duplicated rows in this table. I will fix it ...
4
votes
1answer
1k views

Are we required to handle Transaction in C# Code as well as in Store procedure

Do we really require transaction handling in c# as well database store process both side C# Using(transaction with transaction scope) { Execute store proc; Transaction. Complete; } SQL ...
3
votes
1answer
5k views

gain admin access to sql express 2008

I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or ...
1
vote
1answer
2k views

routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter. the code for the stored procedure is below. the problem: it ...
4
votes
2answers
972 views

Unexpected gaps in IDENTITY column

I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script: CREATE TABLE [dbo].[PONumbers] ( [PONumberPK] [int] ...
1
vote
1answer
956 views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
80
votes
4answers
20k views

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost ...
48
votes
6answers
45k 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 ...