Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms

3
votes
3answers
2k views

T-SQL Calculate average value for Duration Field

Can anyone please help me on this.. I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'. How do I calculate the average duration? I read some other ...
3
votes
4answers
225 views

How to find duration in a dataset

I have a dataset of the following structure: Target, PollTime, Value ------------------------ 1. A, 2013-12-13 13:31:44.000, 15.00 2. A, 2013-12-13 13:32:44.000, 16.00 3. A, 2013-12-13 13:33:44.000, ...
3
votes
2answers
3k views

How can I get a list of column names and types from a resultset?

Say I get a resultset back from the following query: select * from sys.database_files; For any given resultset, I want to be able to query the column names and types so I can then create tables to ...
3
votes
1answer
205 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
3
votes
2answers
238 views

Find top by values by percents using range in SQL Server

What will be easier way to group records by range of percents. Here what I try to accomplish Select top percents between 50 and 75 * from myTable order by PKI Here is way I did this but I have ...
3
votes
1answer
490 views

Get records not updated in last 30 minutes

I am trying to write a script that will get records that have not updated the table in more than 30 minutes. Example records: DNS_NAME LAST_PERF_TIME esxnu01 2013-12-24 12:10:00.000 esxnu02 ...
3
votes
3answers
178 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
3
votes
1answer
996 views

Understanding SIX lock in Microsoft SQL-Server

Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of ...
3
votes
3answers
76 views

I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches

I have the following basic dataset in MS SQL Server 2008R2: create table #temptable ( Source varchar(255), PollTime datetime, Value numeric(20,2) ) insert into #temptable( Source, ...
3
votes
1answer
204 views

How do you run T-SQL commands on multiple servers from one place?

I have some daily and weekly tasks where I run a script to get the required information. What is the way to running this code once and hitting all the servers without having to log into each one of ...
3
votes
2answers
292 views

SET DATEFORMAT problems

I have some doubts about SET DATEFORMAT command. 1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my ...
3
votes
3answers
2k views

Select rows after a specific ID

I have the following query, it chooses from a news articles table. Due to multiple sorting factors, I cannot locate the next page of date. The parameter I have is the last ID in the query result set, ...
3
votes
3answers
864 views

Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
3
votes
2answers
1k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
3
votes
1answer
522 views

Does T-SQL MOVE actually move the database, or just copy?

I'm a bit confused over the T-SQL command MOVE. I have a statement saying: MOVE N'Prod_Database_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf' ...
3
votes
2answers
590 views

Physical Server or a Virtual Machine SQL Server?

Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?
3
votes
2answers
364 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
3
votes
3answers
2k views

Conditional WHERE Clause in a Table Valued Function

I have a table valued function CREATE FUNCTION [dbo].[DateRange] ( @StartDate date, @EndDate date, @Location varchar(25), @Device varchar(25) ) ...
3
votes
3answers
765 views

Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included. Is ...
3
votes
1answer
783 views

LIKE character length limitation

The background to this question can be found here. The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
3
votes
2answers
315 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub sproc based on passed param. if ...
3
votes
2answers
765 views

MS SQL : Use Computed value to Compute other values

I'm having problem with generating results which are computed from data that exists in database. This is the case I have Coupons that can be amount or percent, if percent is 0 then amount is used ...
3
votes
2answers
107 views

Rebuilding indexes on a list of tables

I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the ...
3
votes
1answer
541 views

How to replace “set rowcount”

Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release: http://technet.microsoft.com/en-us/library/ms188774.aspx We have some legacy code ...
3
votes
4answers
503 views

How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
3
votes
2answers
303 views

Grouping Selecting unique values in a single column [duplicate]

Here is my table ID Field1 Field2 Field3 Field5 Field7 357 357 2013-03-07 08:02:02:275 t02 bBCD00103RG 365 365 2013-03-07 08:02:05:307 t02 bR U00103w 374 ...
3
votes
2answers
5k views

Create job to send alert using sp_send_dbmail when table value greater than x

PROBLEM The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached. TABLE CREATE CREATE ...
3
votes
2answers
328 views

What do these statements mean in the MS β exam 70-461 “skills measured” list?

I have registered for the upcoming beta exam 70-461 entitled Querying Microsoft SQL Server 2012. I've been reading through the exam overview and found a bunch of descriptions I'm not exactly sure ...
3
votes
2answers
526 views

Tool to create a TSQL script from a SQL Server 2008 trace file

Are there any tools available to create an executable tsql script from a SQL Server trace file? Is there an easy manual way?
3
votes
1answer
82 views

Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in ...
3
votes
1answer
155 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
3
votes
2answers
3k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
3
votes
1answer
109 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
3
votes
1answer
861 views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
3
votes
2answers
414 views

can I convert an integer to base-2 without a function

Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from ...
3
votes
2answers
1k views

Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. ...
3
votes
1answer
153 views

When to use With Recompile

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc.. Sometimes there are cases that it may help, but OPTION(RECOMPILE) ...
3
votes
1answer
161 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
1answer
2k views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
3
votes
1answer
382 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
3
votes
1answer
3k views

Nonclustered Index still doing index scan

I have this simple query. SELECT a.RecordID SubmissionID, a.[DateSubmitted], a.[FinalAction], a.[FinalActionReason], a.[FinalActionDate], b.GradeSubmissionListID, b.[Action], ...
3
votes
1answer
613 views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
3
votes
1answer
932 views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...
3
votes
1answer
110 views

Relationship with a default value

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title. Here is my current relationship CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL, Name ...
3
votes
3answers
638 views

t-sql - combinatorics

I'm trying to find all possible character combinations in a variable length string. For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--' I think that essentially I need ...
3
votes
4answers
620 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
3
votes
1answer
149 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
3
votes
1answer
609 views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
3
votes
2answers
254 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
3
votes
1answer
54 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...