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

learn more… | top users | synonyms

4
votes
2answers
848 views

Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions. We need ...
4
votes
1answer
74 views

SP for Restoring Database and performing other tasks on it

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
4
votes
2answers
218 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
4
votes
1answer
320 views

T-SQL and GPS co-ordinates

I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...
4
votes
1answer
3k views

How can I create a stored procedure using xp_fixeddrives?

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008 ALTER PROCEDURE freespace AS ...
4
votes
2answers
1k views

Restore all applicable files in a backup device in T-SQL

I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
4
votes
1answer
773 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
4
votes
2answers
460 views

T-SQL Syntax error copying views dynamically

I'm creating a stored procedure to copy all views from one database to another using the following code. ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB ...
4
votes
1answer
4k views

T-SQL: Convert XML to string without self-closing tags

We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so <BOOKING> <BOOKINGID>56812</BOOKINGID> <PERSONCODE /> ...
4
votes
1answer
72 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
4
votes
3answers
3k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
4
votes
1answer
766 views

Calculating Median Value within a Group By

I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is ...
4
votes
1answer
619 views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
4
votes
2answers
261 views

Fulltext search limiting users possibilities

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure. CREATE PROCEDURE [dbo].[SearchPages] ...
4
votes
0answers
103 views

Why is it @@SERVERNAME but not @@DBNAME? [closed]

I'm just curious: To get the server name, I use SELECT @@SERVERNAME but to get the current database name, I use SELECT DB_NAME() Technically, both seem to be scalar functions: @@SERVERNAME is ...
4
votes
1answer
354 views

Tips on improving/refactoring slow query?

Let's play lotto! Ok. I have a numbers table with 80 rows (numbers 1-80): create table dbo.numbers (knum tinyint primary key identity); I have a table with the drawid, and each number drawn. ...
3
votes
3answers
3k 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
231 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
371 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
289 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
3answers
5k views

Inserting rows into other table whilst preserving IDENTITY

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
3
votes
1answer
567 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
224 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
263 views

Delete using a composite key

I am trying to wrap my head around creating a delete statement for a table with a composite key. I would like to create something like: DELETE FROM table_example1 WHERE COLUMN1, COLUMN2 ...
3
votes
1answer
3k views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
3
votes
1answer
1k 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
2answers
267 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
3
votes
3answers
104 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
223 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
2k 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
2answers
9k views

deleting large volumes of rows from table efficiently in SQL Server [duplicate]

I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system ...
3
votes
3answers
958 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
566 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
177 views

sql querying for a pattern

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades. Create Table StudentGrades ( StudentID references Student(StudentID) int not null, ...
3
votes
2answers
1k 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
494 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
947 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
1k 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
1answer
4k views

To use or not use ISNULL(NULLIF(Field1, ''))?

In the past I've seen use of something such as SELECT ISNULL(NULLIF(Field1, ''), 'NewValue') to tersely get a fallback value. However, since the advent of CASE in TSQL, we've favored something ...
3
votes
2answers
336 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
786 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
1answer
140 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
3
votes
2answers
154 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
2answers
3k views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
3
votes
1answer
729 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
1answer
396 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
4answers
667 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
390 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 ...