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)

0
votes
2answers
94 views

What to do with a table with a lot ( 50+ ) indexes? SQL Server 2008

We have a table here, used by our software. Since i'm new in this company ( the new dba here ), what i see is that some tables of some databases have a LOT of indexes ( like 50...60 ). Is this good? ...
0
votes
1answer
121 views

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I have a procedure to backup SSAS Databases that is working successfully. I also delete the SSAS backup files that are older than 2 days. What I am looking forward to implement is: 1) How to verify ...
0
votes
2answers
318 views

New database server hardware and software inserts slower than old setup

We are upgrading our database hardware and software. The previous server is about 5 years old and is running SQL Server 2005 while the new one is SQL Server 2012. Every hardware component is faster in ...
0
votes
2answers
127 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
-1
votes
1answer
913 views

The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?)

The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) becomes too big , ~80 GB. When I checked it, I see that there are a lot of ...
-1
votes
1answer
3k views

pivot a table with cell name as column name

I have a table structure is : year | code | name| value | wef | 2014 | a001 | abe | 2000 | 2014-04-01 | 2014 | a001 | def | 3000 | 2014-05-01 | 2014 | a002 | abe | 2000 | 2014-06-01 | ...
-1
votes
1answer
197 views

Validate each parent intermediary is also a parent to itself

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents ...
-2
votes
1answer
73 views

How to scale a write intesive application with a SQL Server 2014? [closed]

I am studying about many databases lately and the one thing I can't get the hang of is how to scale a write intensive application in SQL Server, I'm talking about native features, not third party ...
-2
votes
2answers
20k views

Display month in columns and the sum of sales below

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
-4
votes
2answers
2k views

Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2 [closed]

MS on this link http://msdn.microsoft.com/en-us/library/ms182776.aspx mentioned that we should use rowversion data type over Timestamps. Now inside my sql server 2008 R2 I want to create a column with ...
-8
votes
1answer
2k views

In SQL Server, how to launch a python script from an insert trigger? [closed]

We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to ...
63
votes
4answers
39k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed ...
24
votes
2answers
12k views

What does a DBA have to know about SSAS?

I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management. From the point of view of administering an instance ...
48
votes
4answers
15k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
27
votes
2answers
1k views

Where can I find database tuning exercises for learning?

As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc. I'm looking ...
31
votes
1answer
20k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...
32
votes
4answers
4k views

On a dedicated database server, how much memory to reserve for the OS?

Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system? I realize this will probably vary somewhat depending on the ...
25
votes
5answers
27k views

SQL Server suspect database?

What do you do when you have a database that is marked as 'suspect'? Restore from the last backup? Please advise. Thank you!
22
votes
4answers
8k 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
3answers
17k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
9
votes
6answers
7k views

Estimate projected database growth

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 ...
23
votes
3answers
7k 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 ...
19
votes
8answers
6k 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 ...
14
votes
5answers
2k views

How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
11
votes
2answers
11k views

Performance difference between Clustered and Non Clustered Index

I was reading Clustered and Non Clustered Indexes. Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. Non Clustered ...
10
votes
3answers
7k views

CHECKPOINT or COMMIT writes to disk?

Let's say, for SQLServer2008R2 and higher, with full recovery mode databases. I always thought : When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM. ...
19
votes
2answers
1k views

Do I need separate indexes for each type of query, or will one multi-column index work?

I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic. My basic understanding is that generally speaking, a single index that ...
11
votes
4answers
14k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
10
votes
2answers
39k views

Returning a result set with multiple rows based on max date

I have a child table that is something like this: [Cust Date Table] | Customer ID | Some Date | Balance | +-------------+------------+---------+ | 1 | 2012-04-30 | 20.00 | | 1 ...
27
votes
3answers
31k views

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM ...
26
votes
4answers
3k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
25
votes
3answers
14k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
18
votes
3answers
5k 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, ...
17
votes
3answers
2k 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 ...
17
votes
3answers
6k 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, ...
15
votes
9answers
2k 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 ...
13
votes
3answers
5k 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 ...
13
votes
4answers
9k 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 ...
13
votes
4answers
3k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
11
votes
3answers
13k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
10
votes
4answers
26k views

How does paging work with ROW_NUMBER in SQL Server?

I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
7
votes
4answers
63k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
5
votes
3answers
1k views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
31
votes
5answers
6k 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 ...
21
votes
2answers
19k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
18
votes
3answers
2k 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
1answer
17k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
13
votes
1answer
6k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
13
votes
2answers
8k views

Broken NTFS Permissions for MSSQL$SQLEXPRESS

I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created. D:\SQL D:\SQL\Data D:\SQL\Backup etc.. I kept the ...
13
votes
3answers
2k 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 ...