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)

0
votes
0answers
84 views

Query plan reads vs statistics io reads

I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the ...
8
votes
3answers
4k views

Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other ...
-3
votes
2answers
5k 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 ...
1
vote
1answer
344 views

How to retreive deleted records in a trigger on a table with composite primary key

I am trying to optimize a trigger on a table in a database from a 3rd party. I am not allowed to change or add any columns to the schema. The table has a composite primary key, one column is an ...
0
votes
0answers
24 views

Cannot add “rowversion” data type in sql server 2008 r2 management studio, or even to modify existing column data type [duplicate]

I want to create a column with Rowversion data type, but inside my SQL Server 2008 R2 Management Studio, I cannot find such a data type: Also when I tried altering existing column named timestamp ...
0
votes
0answers
37 views

Remove dupes from joins

I am trying to get all accounts with duplicate end dates. I need info from four tables that only have the FK LDC_ACCT_ID in common. When i join the tables i get tons of dupes. I understand why but I ...
0
votes
1answer
809 views

Breaking a big insert query into smaller ones

I have the below INSERT query. It tries to insert over 50,000 rows and it fails because of logging. Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT ...
1
vote
1answer
3k views

The process is running and is waiting for a response from the server - transactional replication

I could not yet figure it out why one of my transaction replication subscription is constantly coming out with the following annoying message: The process is running and is waiting for a response ...
3
votes
1answer
667 views

Computed column 'IsWeekend' in table <table_name> cannot be persisted because the column is non-deterministic

I'm in the process of tuning a query in SQL Server 2012 that is spilling to tempdb due to an incorrect cardinality estimation when using the DATENAME() function on a column to check if the data is ...
1
vote
1answer
184 views

How do I get the count for time periods?

I have a table with the following columns. [ComputerGroupid], [Computerid], [Userid], [loginTime], [logoffTime] And my requirement is to get the table with the below information ...
0
votes
1answer
38 views

join on just the next closest date?

I want to get a table of [file], [folder], [DateAdded], [DateRemoved] I have a log table of changes that has [file], [ChangeDate], [OldFolder], [NewFolder] Files have moved in and out of ...
2
votes
1answer
1k views

Results for every hour of the day even data if not present

I am trying to write a query to show the count of users for each hour of the day. If there is no data for that hour, I want to record the hour with a count of zero. Source data ...
5
votes
1answer
128 views

SQL Native backup MIRROR TO performance

We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups ...
4
votes
3answers
1k views

Help with SQL Query to XML output

I have the following query: SELECT [ID] AS [NewID] , ( CASE WHEN LEN([MiddleName]) > 0 THEN [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.' ...
0
votes
1answer
31 views

Merging 2 queries together

I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before: tdate period staff ...
0
votes
2answers
776 views

Suspended query causing high CPU usage

Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver. Issue: My online DB server was experiencing high CPU usage. Using the ...
1
vote
3answers
3k views

SQL Query in suspended state causing high CPU usage

Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver. Issue: My online DB server was experiencing high CPU usage. Using the following ...
17
votes
2answers
274 views

In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?

Update 2014-12-18 With the overwhelming response to the main question being "No", the more interesting responses have focused on part 2, how to solve the performance puzzle with an explicit ORDER ...
1
vote
1answer
39 views

transaction log restore

we have an external production database (maintained by another company) and we are trying to build an internal database that replicates the production database for reporting purposes (both ...
1
vote
2answers
1k views

Filling in date holes in grouped-by-date SQL data

The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue: -- create example table DROP TABLE dbo.EventRecords GO CREATE TABLE dbo.EventRecords ...
4
votes
2answers
418 views

Backup Database Task - What's the number at the end of the filename?

When using a SQL Server 2012 EMaintenance Plan for backup purposes, the Backup Database Task creates a unique filename. I understand the format all the way to the final set of numbers. For example, a ...
5
votes
1answer
169 views

How do I determine the spid of the session that is making a query on a linked server?

I have a session running on a SQL Server instance that is blocking other sessions (holding on to a lock without releasing it). Querying sys.sysprocesses yielded the hostname of a different SQL Server ...
46
votes
6answers
7k views

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
0
votes
0answers
165 views

Is my SQL Job causing the High Disk usage?

I have a concern regarding High Average disk queue length for one of my server with below configuration: Version: SQL 2005 with SP3, standard edition. windows: version 2003 r2 SP2 No Processors= 4 ...
0
votes
1answer
65 views

The purpose of transaction log in Restore First

Based on this article , the golden role for restore first After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get ...
1
vote
0answers
221 views

Why is Update Query Slow with correct Indexes?

I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query. However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is ...
10
votes
1answer
543 views

Visualizing SQL Server Extended Events data

Lately I've been exploring using Extended Events in SQL Server to help me benchmark and optimize various queries. So far, to view the event data I have been using the "Watch Live Data" feature in ...
24
votes
3answers
3k views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
5
votes
1answer
32k views

Installing SQL Server 2012 - Wait on the Database Engine recovery handle failed

I am installing SQL Server 2012 SP1 on a new Windows 8.1 machine. During installation I get this error: (Screenshots similar to this question) TITLE: Microsoft SQL Server 2012 Service Pack 1 ...
-1
votes
1answer
29 views

Sorting a table based on nullable Position field that has to match the position on the result set

I'm using SQL Server 2012 I have a simple table that has a Nullable (int) Position and a (datetime) Created field. I would like to do a select * from this table order by created desc but ...if a ...
1
vote
1answer
146 views

Delete back ups more than 2 weeks in sql server

I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario? EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASE', @Directory = ...
0
votes
1answer
88 views

Update and Insert Statement taking forever

I have a database with two columns that is called using the following query: SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC In my application it is ...
0
votes
1answer
298 views

Selecting row multiple times, as specified by a field in that row

As part of an redesign, there's need for an different schema in one of the main tables in our database. The data in one of the existing tables will be kept as legacy data. To make the changeover ...
3
votes
1answer
54 views

What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2

This is not urgent, just curiosity. QUESTION: I was trying to troubleshoot a slow server today, and I came across a high figure for a wait type called PREEMPTIVE_REENLIST. I'm not able to find this ...
4
votes
4answers
41k views

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I am trying to run a simple query to get all rows created in November: SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' AND '2014-11-30 23:59:59.997'; SMSS ...
2
votes
2answers
690 views

Lines starting with colons in SQL Server

Playing around with DB projects, Visual Studio generated a change script with the following lines: GO SET ANSI_NULLS, ANSI_PADDING --etc.; SET NUMERIC_ROUNDABORT OFF; GO :setvar DatabaseName "Foo" ...
2
votes
3answers
930 views

Releasing Free Space

I am running a rather large database on SQL Server 2008 R2 Express. Recently I hit the 10GB limit, so the system would not allow me to create anymore objects, ect.. I have archived off a very large ...
2
votes
1answer
105 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
1
vote
1answer
46 views

Create a log file for check which time import has been done and to split the import files

How to create a log table for when does the last import has been done and also for Query for split the records in to 1000 while importing. to avoid the bulk insert For example : If the data ...
4
votes
2answers
142 views

Using Database Engine Tuning Advisor in production

I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will ...
0
votes
5answers
223 views

I do not understand SQL Server Transaction Logs

I understand that a Microsoft SQL Server Database has two components: A database file A transaction log file Whenever I make a change to a table, does it get written to both the database file AND ...
7
votes
1answer
401 views

Enabling TDE without breaking existing mirror

I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended ...
0
votes
1answer
360 views

Small business SQL Server [closed]

I'm asking the experts.. I work in a small business (<10 employees) but I need a production database. I wouldn't go as to say I'm a DBA but I well versed in creating databases, tables, ...
3
votes
1answer
1k views

Alter table and update in sql server transactions

I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a ...
4
votes
1answer
2k views

for xml missing null columns

When we use for xml clause to create xml from a row like as follows select * from Menus where MenuID=100 for xml path('') The xml will miss the columns which have NULL as the value. I have a found ...
1
vote
0answers
434 views

“Skipping the default startup of database” SQL notification for AlwaysOn Cluster

So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, ...
2
votes
2answers
170 views

What SQL Server database version do I need?

I am not a database experts but part of the project I am currently interested in accepting requires to do a DB replication. The system will have many outlets... says Site A, Site B, Site C, etc.. ...
2
votes
1answer
165 views

Threshold for sort spill and random cardinality estimates

I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014: USE tempdb IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest CREATE TABLE tblTest ( ...
-1
votes
1answer
1k views

How to know which columns value is different between two rows

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 ...
1
vote
1answer
1k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...