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

learn more… | top users | synonyms

5
votes
2answers
220 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 ...
5
votes
2answers
1k views

How to change SQL sever configuration manager settings using TSQL?

SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc... Is it possible to make these changes that is done in SQL server ...
5
votes
1answer
491 views

Passing a literal ? to sp_MSforeachdb

I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
5
votes
1answer
996 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
5
votes
1answer
103 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
5
votes
1answer
543 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
5
votes
3answers
2k views

Total of orders open on a given date for each date in a date range

This question is similar to Running total with count?, but please allow me to explain some further twists on the issue. I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
5
votes
1answer
2k views

Using FLOATs with RAISERROR

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
5
votes
2answers
5k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
5
votes
3answers
889 views

How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
5
votes
1answer
666 views

Why can the target of the OUTPUT INTO clause not be a view?

Here is my statement: DELETE FROM table1 OUTPUT deleted.col1, deleted.col2 INTO view1; It gives me this error: The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
5
votes
1answer
2k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
5
votes
1answer
3k views

How to detach corrupt database in SQL Server

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database. Is this safe way to do that: DECLARE ...
5
votes
2answers
484 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
5
votes
0answers
87 views

SQL Server - Best practices on debugging extremely complex stored procedures [closed]

I recently inherited a MESS of a search. It's around 10,000 lines of code in the procs/functions alone. This search is aptly named the "Standard Search." It's a proc that calls about 6 other procs, ...
5
votes
1answer
784 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
6answers
4k views

Select rows with same id but null and some other value in another column for that id

I want to get only rows having a value NULL and some other value than NULL for a particular username column. If both rows have null for that particular username or both have some values other than ...
4
votes
2answers
809 views

Escaping T-SQL Keywords

$sql = "SELECT Kill FROM tbl_pvporderview"; Problem is that I end up with: Incorrect syntax near the keyword 'Kill'. Because kill is a T-SQL command... any way to bypass it? I can't change the ...
4
votes
3answers
19k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
4
votes
2answers
142 views

Difference Between SP with BEGIN/END and without BEGIN/END

I have found some SP(stored procedure) written as, CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) BEGIN --- Actual Work END and some as CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) --- ...
4
votes
1answer
4k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
4
votes
2answers
9k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
4
votes
2answers
5k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
4
votes
4answers
425 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 ...
4
votes
2answers
448 views

Truncating table without using Alter Table permission

So my DBA don't want developers to give Alter table permission. Now one of the procedure that we created requires to Truncate tables (since apparently delete only removes records, but truncate frees ...
4
votes
2answers
12k views

CASE ORDER BY with multiple columns, and different sort options

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT. @Orderby = 1 Then Date column should be ASC @Orderby = 2 Then Date column ...
4
votes
1answer
809 views

SELECT INTO creates a new table?

I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
4
votes
3answers
541 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) + '.' ...
4
votes
3answers
374 views

Splitting a deck of cards, and returning the results as if they were shuffled once

Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a: select ...
4
votes
2answers
968 views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
4
votes
3answers
3k 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, ...
4
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) ) ...
4
votes
1answer
137 views

SQL “Standard” guide?

I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?
4
votes
2answers
1k views

Update Statement takes forever to complete [closed]

I have table around 1250000 records, and 80 columns Table Structure CREATE TABLE [dbo].[Table] ( [Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] ...
4
votes
1answer
3k views

Mapping of multiple server logins to one database user

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology). Is it possible to log multiple ...
4
votes
2answers
575 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
4
votes
2answers
792 views

Why can't I view the SQL Server database created by a web application?

I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity ...
4
votes
1answer
15k views

Split/explode comma delimited string field into SQL query

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216' and I want to use it to search IN this field: SELECT * FROM table1 WHERE id IN (id_list) id is integer id_list is varchar/text ...
4
votes
1answer
223 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting ...
4
votes
1answer
414 views

Is it possible in SQL Server to change default “order by” direction from asc to desc?

This question came up in a discussion at work... given the following T-SQL statement: select firstname, lastname from users order by lastname; the default sort order on the order by clause is asc. ...
4
votes
1answer
145 views

Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

Expressions like NOT IN (1,2,3) are valid in a where clause: SELECT foo FROM bar WHERE ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22)) However if I want to move an elaborate expression ...
4
votes
1answer
40 views

Does NTILE work over floating point numbers?

I am trying to use NTILE on my data using a float data type in my PARTITION BY clause. Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through ...
4
votes
1answer
319 views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. ...
4
votes
2answers
3k views

Query two SQL Server databases in same function but on two different servers

Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases. Is it possible to query both ...
4
votes
3answers
673 views

Copy 8 Million Records

I have a table in a local MS SQL Server database that has 72 columns and over 8 million records. Most of the columns are floats except for the primary key which is an INT Identity column. The table ...
4
votes
1answer
13k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
4
votes
1answer
140 views

Null Values in a CASE statement

I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to ...
4
votes
1answer
255 views

How do you get an average of job history times?

The following is a script I found online that has most of the elements of what I desire in the result set. The only thing missing is how I can get an average of the run times over a decided time ...
4
votes
1answer
4k 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 ...