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

learn more… | top users | synonyms (1)

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

Custom ID Numbering - fast way on insert?

As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern. I need to assign custom ID numbers for markers during loads. I can not use an identity field, ...
5
votes
2answers
1k views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , SUM(...
5
votes
1answer
443 views

How can I make this query sargable?

I have a query which joins two tables based on a timestamp value (don't ask). One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar. Previously,...
5
votes
1answer
499 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
5
votes
1answer
306 views

Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?

The Setup: Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink. Now the Transaction Log grows so quickly during the ...
5
votes
1answer
845 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 ...
5
votes
1answer
152 views

Why does this query not use the index that exists on SURNAME?

There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used. DECLARE @SURNAME VARCHAR(30) = 'test' SELECT SURNAME FROM PATIENTS AS PAT WHERE (PAT....
5
votes
2answers
840 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 ...
5
votes
1answer
4k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN a....
5
votes
2answers
3k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
1answer
254 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...
5
votes
1answer
372 views

Incorrect syntax near 'role' with generated script

I've recently had to recreate a user several times. To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window. However, after ...
5
votes
1answer
802 views

Get minimal logging when loading data into temporary tables

Even after reading The Data Loading Performance Guide , I am still unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get ...
5
votes
2answers
344 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
5
votes
2answers
18k 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 ...
5
votes
3answers
3k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
5
votes
2answers
102 views

RIGHT() works in a SELECT but not in an UPDATE?

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
5
votes
1answer
219 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
5
votes
2answers
239 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
5
votes
2answers
2k views

Listing SQL Server instance startup parameters

I have a server I have just restarted and verified which trace flag are active using DBCC TRACESTATUS: Trace Flag : 3688 Function: Removes messages to errorlog about traces started and stopped ...
5
votes
3answers
8k 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 #...
5
votes
1answer
271 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
5
votes
2answers
4k 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] ...
5
votes
1answer
21k 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 ...
5
votes
1answer
289 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 (...
5
votes
3answers
1k 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 ...
5
votes
1answer
334 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
5
votes
2answers
367 views

Using Left or substring with wildcard in SQL

I am looking to retrieve information from a field, but am having troubles getting exactly what I want. The information I want has no set order and I am also looking for two different things. Here is ...
5
votes
2answers
4k views

How to extract pipe-delimited data using BCP?

I tried the following command USE <DBNAME> SELECT 'exec master..xp_cmdshell' + ' ''' + 'bcp' + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' out' + ' D:\' + TABLE_NAME + '....
5
votes
2answers
320 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
1answer
186 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 ...
5
votes
1answer
762 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
2answers
8k 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
2answers
46 views

SQL Server 2008R2 - MAX Result and Hour per Day

I have a table full of network interface statistics. InterfaceID TimeStamp RCV_bps XMT_bps ----------- ------------------------------ ------------- ------------- 275 ...
5
votes
3answers
244 views

Charindex, Getting specific data from a string

Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error. I searched for similar problems on stackexchange as well as a ...
5
votes
1answer
1k 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 MAX([ShipWorks].[...
5
votes
1answer
132 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
703 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
877 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 ...
5
votes
2answers
96 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
5
votes
2answers
3k views

How to change default database to all logins?

I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login ...
5
votes
2answers
707 views

Update strings for all cmdexec Jobs

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server? We'd like to change our logging location, and in principle I'd think this could be ...
5
votes
1answer
1k views

Calculating Median Value within a Group By

I have a Stored Procedure 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 ...
5
votes
3answers
1k 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
776 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
2answers
71 views

SQL Server - Stopping a server side trace

I want to setup a server side replay trace and scripted out setting up the trace. However I want the trace to end either after 12 hours or once 10 1GB files have been created. Normally with a max file ...
5
votes
1answer
116 views

Compound Primary Key - Index both fields?

I have a many-to-many join table between users and roles. CREATE TABLE AppUserRole ( UserId INT NOT NULL, RoleId INT NOT NULL, CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId), ...
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] ( ...