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)

2
votes
1answer
1k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
0
votes
2answers
8k views

How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine... I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. ...
89
votes
5answers
56k 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 ...
50
votes
5answers
16k views

Which database could handle storage of billions/trillions of records?

We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
24
votes
2answers
5k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
32
votes
4answers
20k views

Clustering vs. transactional replication vs. availability groups

Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails. As a developer and not a DBA, I ...
24
votes
2answers
4k views

As a DBA, how would I go about transitioning from Oracle to SQL Server?

I'm an Oracle DBA that also has Sybase experience. What are the major architectural and conceptual differences between the two RDBMS platforms? An answer similar to the SQl Server->Oracle question ...
31
votes
5answers
36k 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!
17
votes
1answer
29k views

SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this ...
23
votes
4answers
9k 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 ...
17
votes
4answers
116k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
14
votes
2answers
51k views

High Disk I/O from sql server or is High disk I/O slowing sql server?

I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server. Normally everything is fine, however over the past few weeks we have been having huge lag spikes in ...
26
votes
1answer
8k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
15
votes
3answers
6k 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 ...
20
votes
1answer
16k 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 ...
20
votes
4answers
10k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
14
votes
2answers
8k views

What is a Clustered Index?

I need a short explaining of clustered index. What is a clustered index? What are best practices for using the clustered index?
13
votes
2answers
7k 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 ...
14
votes
3answers
3k views

Is there any benefit to defragmenting SQL indexes in a SAN environment?

Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records. We have been running Ola Hallengren's index maintenance scripts weekly, and ...
14
votes
3answers
13k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
12
votes
1answer
16k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
12
votes
2answers
11k views

What is blocking and how does it happen?

I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
11
votes
1answer
16k views

When is a Dynamic Port “dynamic”?

I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work. First question: If the IPALL TCP Dynmaic Ports setting is a specific ...
5
votes
5answers
23k views

Cannot start SqlLocalDB instance with my Windows account

I'm the administrator and simply run the command: sqllocaldb start v11.0 Result: Start of LocalDB instance "v11.0" failed because of the following error: Error occurred during LocalDB instance ...
19
votes
2answers
4k views

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table ...
16
votes
3answers
8k views

Find out what queries are causing the biggest amount of network traffic

On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what ...
10
votes
2answers
114 views

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see ...
10
votes
4answers
27k views

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not ...
10
votes
2answers
7k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
9
votes
2answers
11k views

SQL Server 2000 on Windows 10

I need to install SQL Server MSDE 2000 SP4 on Windows 10. I have other machines that run Windows 7 and 8.1 and SQL Server MSDE 2000 works fine (with merge replication configured too). Now I testing ...
7
votes
3answers
541 views

sql server: updating fields on huge table in small chunks: how to get progress/status?

We have a very large (100million row) table, and we need to update a couple of fields on it. For log shipping, etc, we also, obviously, want to keep it to bite-size transactions. Will the below do ...
7
votes
1answer
1k views

Query is slow in SQL Server 2014, fast in SQL Server 2012

During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues. One of the queries that completes within seconds on SQL ...
7
votes
2answers
1k views

How to schedule jobs in Sql server 2008 for less than 10 sec?

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds. The job is used to insert/update visitor information, and segmentation ...
7
votes
1answer
769 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
4answers
2k views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
10
votes
3answers
3k views

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? ...
9
votes
2answers
40k 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 ...
8
votes
1answer
836 views

The use of NOT logic in relation to indexes

According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development: Neither leading wildcard characters not NOT logic allow the query optimizer to ...
7
votes
3answers
4k views

SQL Server - what isolation level for non-blocking select statements?

I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
6
votes
1answer
14k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
6
votes
3answers
14k 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
2answers
2k views

SSRS Blocking Processes

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes: [dbo].[CheckSessionLock] [dbo].[WriteLockSession] We are aware that some ...
4
votes
1answer
3k views

Restore All Databases Script

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample ...
4
votes
2answers
5k views

SQL Server how to detect unused views and/or get usage stats

Is there a way to determine in SQL Server if a view is no longer being used? (without removing it) Ideally i would like to know usage for SQL Server 2000 as well as 2012 if that is possible. I am ...
4
votes
2answers
29k views

How to Find the Foreign Key Associated with a Given Primary Key

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM ...
3
votes
3answers
2k views

Restore a large number of transaction logs

I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > ...
3
votes
2answers
2k views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
14
votes
5answers
18k views

.bak file not visible in any directory in SSMS

I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it. I ...
14
votes
4answers
31k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
14
votes
3answers
16k 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 ...