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)

15
votes
8answers
2k views

Reasons for avoiding large ID values

We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She ...
2
votes
1answer
86 views

Investigating specific SELECT queries

For a particular row in a table how can I determine the number of reads done to it such as SELECT * FROM MyTable WHERE ID = <something> and when and where the read query executed? I'm ...
3
votes
3answers
456 views

Login lifespan for password on SQL Server

What is the "lifespan" for a login with the option Enforce Password Expiration Enabled? Where can I find configuration/option to change the time?
0
votes
2answers
214 views

Need help finding inactive users in my database

I am trying to figure out how to get this figured out. Problem: Users of inactive customers are receiving scheduled maintenance notifications Indicators: Organizations have a custom binary field ...
-5
votes
1answer
91 views

Finding users in the database owner role [duplicate]

I need a script which will list out all the users with the database owner permission for a specific database.
3
votes
1answer
125 views

Effects of turning the transaction safety OFF

I am considering to turn off the transaction safety to maximize the performance. Before that i am interested in finding the current status of transaction safety. Is there any command that we can get ...
1
vote
1answer
124 views

Transactional Replication - Replication Order of Related Tables

** This was previously asked on Stackoverflow - I removed it from there and recreated here ** I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be ...
2
votes
2answers
2k views

Can a temporary table be shared across sessions?

I have revised a procedure that builds a dynamic SQL statement to create and populate a temporary table. The syntax was something like this: ...'create #temp_' + CAST(GETGUID() AS VARCHAR(36)) ... ...
0
votes
2answers
2k views

How can i know the relationship in the database in sql server 2008? [closed]

I want to know how can I determine relationships between data which is not determined by the primary key in the design. For example, I have three tables in the database like this :- table 1: fields ...
0
votes
1answer
508 views

Run CDC Cleanup job at specified time

I have enabled cdc and using it , it works fine now the problem is that we have many tables in a database and also we have many databases so how to configure cdc cleanup job to run again and again ...
2
votes
0answers
255 views

Moving data from MS Access to MS SQL Server

I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of ...
0
votes
0answers
167 views

Data warehousing: data transfer process too slow with larger batches

I have two primary instances of SQL Server, one for OLTP and one for reporting. The data warehouse for reporting is basically a Type 2 SCD: every table has DWDateStart and DWDateEnd columns and the ...
0
votes
2answers
1k views

How to restart the distributor agent of transactional replication?

After troubleshooting query time out error I would like to restart my distributor agent. So I go to my distributor server, in my distributor DB (called distribution_SA) I run the following query: ...
1
vote
1answer
99 views

Deleting duplicate rows with condition [duplicate]

I have posted a similar question before, but this one is a little modified. I have a table with 4 columns as shown below. ITEMCHARGE CPT4 EFF_DATE CPT4MOD 326.8 29075 ...
1
vote
1answer
276 views

Powershell Restore Azure SQL Database to local with Restore-Database

I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac ...
3
votes
2answers
796 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
5
votes
2answers
283 views

T-SQL brain teaser: find non-overlapping sets

I would like to ask for help coming up with a query, which can identify groups on non-overlapping records. Here is a sample scenario (admittedly contrived). Let's say I have employees who are ...
-1
votes
1answer
38 views

How to manage two tables in SQL Server [closed]

i have two tables offer1 and offer2 The two tables are Offer1 Offer2ID|OfferName|OfferExpiryDate Offer2 Offer2ID|OfferName|OfferExpiryDate now i have third table which keeps record ...
1
vote
2answers
356 views

Deleting duplicate rows with condition(Using SQL and LINQ)

I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. Deletion Condition: When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, ...
0
votes
1answer
558 views

Corrupt DB for a noob

Please help me! I am not a DB admin, and I have been thrust into the role after taking a new job. I need as much step by step as you can give me... I've already done the dbcc ...
1
vote
1answer
248 views

database design for simple comment

I have a following given scenario. A user can have zero or more item. A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for ...
5
votes
2answers
102 views

New Index on table cause table to be locked really often

Here are my tables : CREATE TABLE [dbo].[Trackers] ( [IdTracker] INT IDENTITY (1, 1) NOT NULL, [IMEI] NVARCHAR (16) NULL, [CreationDate] ...
1
vote
1answer
117 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
6
votes
2answers
439 views

Why doesn't a delete increase unused space in a table?

If I populate the following table: IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL DROP TABLE dbo.CleanTableTest; GO CREATE TABLE dbo.CleanTableTest (FileName nvarchar(4000), ...
3
votes
1answer
883 views

How do I identify the program that called a stored procedure?

As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they ...
0
votes
1answer
201 views

Turning multiple fields in column to row

I have an issue that has really stumped me in SQL Server. I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something ...
2
votes
0answers
195 views

Exclude a User From an Audit

I need to set up a SQL Server audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb. In ...
3
votes
2answers
591 views

How to identify unused databases on my sql server

I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server. I went through many articles, which say possibly to go for ...
2
votes
1answer
262 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
0
votes
1answer
105 views

How to delete row from table later than date?

I have a simple table: MyTable: -Id int primary key -Name varchar(MAX) -Deleted bit Now i want delete rows created later that 15.12.2014. But how can you see i don't have a any date columns in my ...
1
vote
1answer
27 views

Dynamic Condition Creation function

I have a function which needs to create a dynamic where clause by reading the columns from the temporary table . CREATE FUNCTION [dbo].[GetWhereClause] ( @TempTableName nvarchar(600) ) RETURNS ...
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
341 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
804 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
664 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
775 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
270 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 ...