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
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
89 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
768 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
7k 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
3answers
1k 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
3answers
6k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
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
111 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
982 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
150 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
2answers
645 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 ...
6
votes
1answer
200 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
22k 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
153 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
1answer
426 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 ...
6
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 ...
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
4k 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 ...
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
886 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
2k 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
1answer
666 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
184 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
844 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
339 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
436 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
208 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
493 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
207 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
250 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
297 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
205 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
990 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
434 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
899 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
100 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
486 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
2answers
15k views

How can I strip non-numeric characters out of a string?

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One ...
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
841 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 ...