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)

20
votes
4answers
11k 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 ...
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 ...
15
votes
3answers
14k 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 ...
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 ...
12
votes
4answers
29k 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 ...
11
votes
1answer
17k 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 ...
3
votes
2answers
480 views

SQL Backup tuning large databases

I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases ...
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
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 ...
10
votes
4answers
32k views

Is there a way to generate table create script in TSQL?

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
10
votes
2answers
125 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 ...
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
2answers
20k views

SQL Server becomes slower over time until we have to restart it

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
7
votes
3answers
619 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 ...
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 ...
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 ...
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? I'...
9
votes
2answers
42k 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
852 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 ...
7
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
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
6k views

Why is SQL Server memory showing in task manager

I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that ...
4
votes
2answers
30k 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 INFORMATION_SCHEMA....
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 ...
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 ...
1
vote
2answers
16k views

How to select top 10 records from each category [duplicate]

I have a table with fields Hour, PathId, Duration, Event, CellId, Channel I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to ...
16
votes
5answers
19k 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 ...
15
votes
4answers
6k views

Is it possible to backup and restore part of a database in sql-server?

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
12
votes
3answers
33k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
11
votes
1answer
1k 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 ...
11
votes
5answers
11k views

How to revive SQL Server 2008 R2 performance counters?

I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys....
10
votes
3answers
903 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
8
votes
1answer
4k views

Trouble With Database Permissions for sp_send_mail

I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows: SELECT SUSER_NAME(), ...
7
votes
2answers
394 views

Automation of backups of Large Number of Database

In a multi-tenant web application where a database is set up for each tenant, the database can be backed up and restored manually through a fairly conventional approach. I am considering this type of ...
7
votes
2answers
938 views

SQL Server : Transactional Replication Computed Column

I am a subscriber to a transactional replication subscription. I have no control over the publisher. I have read the following article about schema changes to the publication database: Make Schema ...
7
votes
2answers
4k views

'Conversion failed' error with ORDER BY CASE expression

I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'. I get the following error: Conversion failed when converting the nvarchar value 'SK11270' to ...
6
votes
4answers
6k views

SQL Server script to delete accounts no longer in Active Directory

We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy ...
6
votes
1answer
848 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 ...
4
votes
3answers
7k views

How to move data from big table

There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?
3
votes
1answer
160 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 ...
8
votes
2answers
3k views

How to backup/restore SQL Server database from command line similar to mysql

I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order ...
8
votes
2answers
583 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
6
votes
2answers
10k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
6
votes
2answers
436 views

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows ...
5
votes
2answers
7k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...