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

learn more… | top users | synonyms

7
votes
1answer
590 views

CATCH block is triggered when it should not

On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
7
votes
2answers
685 views

Does MERGE use tempdb?

Consider the following query: MERGE [Parameter] with (rowlock) AS target USING (SELECT @AreaId, @ParameterTypeId, @Value) AS source (AreaId, ParameterTypeId, Value) ON ...
7
votes
1answer
4k views

Find all ancestor nodes of a HierarchyId using a single SQL statement?

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
7
votes
3answers
4k views

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
7
votes
1answer
480 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
7
votes
1answer
5k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
7
votes
1answer
119 views

Trouble with SQL and aggregates

With this query: SELECT SUM(qty) AS sumQty, cnetprodid FROM quoteitem WHERE ordered = 1 AND sageSOPOrderReturnLineID IS NOT NULL AND LEN(LTRIM(RTRIM(cnetprodid))) > 0 ...
7
votes
1answer
91 views

sysschedules datatype choices

I am Curious. Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
7
votes
1answer
1k views

Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
7
votes
3answers
853 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
6
votes
5answers
8k views

Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Why does something like this not work? SELECT CASE WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL ELSE Somecol END AS MyTest FROM Customers; I am just checking if the column ...
6
votes
4answers
174 views

Compare 4 or more columns values

I have a table like: ID A0 A1 A2 A3 A4 14 A B A C A 15 A A A A A I need to compare all 5 columns and check if they have same value and retrive the only one that have all same ...
6
votes
3answers
2k 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 ...
6
votes
3answers
4k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
6
votes
4answers
2k views

How does SQL Server determine precision / scale?

I'm running SQL Server 2012 SELECT 0.15 * 30 / 360, 0.15 / 360 * 30 Results: 0.012500, 0.012480 This one is even mor confusing to me: DECLARE @N INT = 360 DECLARE @I ...
6
votes
4answers
1k views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
6
votes
2answers
143 views

How to move a fairly large tsql table from one server to another server?

I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box. They are at separate companies without an intranet linkage and obviously without linked ...
6
votes
2answers
1k views

T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
6
votes
2answers
154 views

Hindrance in TSQL SVF Call

I have a simply Products data table with 100k records (just generates some random data): set nocount on create table dbo.temp_Products ( [ProductID] int, [Type] tinyint, [Price] float, ...
6
votes
1answer
5k views

Difference between EXEC() and sp_executesql with no parameters?

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server. But, I'm wondering what the difference is ...
6
votes
1answer
209 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
6
votes
2answers
23k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
6
votes
1answer
157 views

What is the difference between <> All and Not In

Could you please tell me the differences between select CustomerId from Customer Where TerritoryId <> All(select TerritoryId from Salesperson) select CustomerId from Customer where TerritoryId ...
6
votes
2answers
8k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
6
votes
1answer
38 views

I want to add a user/login to role in another database without switching databases

I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole ...
6
votes
4answers
4k views

T-SQL command to log in as a different user?

Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?
6
votes
3answers
4k views

RETURN statement before DEALLOCATE CURSOR

Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure Consider this example: CREATE PROCEDURE [dbo].[test] @ReturnEarly BIT = 0 AS BEGIN SET NOCOUNT ...
6
votes
2answers
921 views

Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
6
votes
1answer
3k views

Restore a Database to a New database (T-SQL)

I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
5
votes
2answers
645 views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
5
votes
2answers
7k views

Transact SQL using WITH in CREATE VIEW

I want to create VIEW using WITH clauses, but really can't find any references on correct syntax. I want smth like this WITH TempTbl AS (SELECT ...) CREATE VIEW SomeView SELECT * FROM TempTbl And ...
5
votes
1answer
702 views

Use of FROM keyword on DELETE statement

The BOL and many other sources point out: FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited. I'm used to ...
5
votes
2answers
186 views

'+' operator with one operand!

How does '+' operator behave in following statement? select + 'taco'; --Result is 'taco' Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
5
votes
2answers
886 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 , ...
5
votes
4answers
1k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
5
votes
1answer
353 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. ...
5
votes
1answer
446 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
3answers
1k views

Dynamic database reference in T-SQL scripts

We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively). Inside foo family of databases there's a view that should ...
5
votes
1answer
216 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
444 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
5
votes
1answer
531 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
5
votes
2answers
220 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
2k 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
3answers
2k 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
1answer
252 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
1answer
307 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
214 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
466 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
960 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 ...