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

learn more… | top users | synonyms (1)

9
votes
2answers
4k views

How to flatten results of a table with two related “many” tables?

I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them. I have the following tables (somewhat abbreviated ...
9
votes
1answer
310 views

statistics are up to date, but estimate is incorrect

When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698: For this query: SELECT * FROM Reports_Documents WHERE ReportID = 18698 ...
8
votes
5answers
11k 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 ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
3answers
1k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
8
votes
3answers
4k 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 ...
8
votes
2answers
3k views

Why should a CTE start with a semi-colon?

I was just looking at a post on SO (http://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table) where Aaron Bertrand proposes using a CTE instead of a numbers table, which ...
8
votes
3answers
4k views

Using Row_Number to find consecutive row count

I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row If my data looks like this 724 727 728 733 735 737 743 747 ...
8
votes
3answers
207 views

What is lost when I create a Foreign Key using `WITH NOCHECK`?

I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate. And if it is not trusted (like when I create the FK using WITH NOCHECK) ...
8
votes
2answers
203 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
2answers
11k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
8
votes
2answers
871 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 ...
8
votes
3answers
354 views

Updating a WHERE clause to check if a value is NOT in a separate table

I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al). The query is: SELECT DATENAME(DW, [AtDateTime]) AS [Day of Week] ...
8
votes
2answers
143 views

SQL Server 2008: sequence which restarts daily

I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are ...
8
votes
1answer
161 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
8
votes
2answers
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 ...
8
votes
1answer
97 views

How can I get the hierarchical values from the below query?

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID. I need to get all the category IDs and their subcategory ...
8
votes
1answer
348 views

Variable for element name

I have this tSQL code which works OK: SELECT c.logguid, a.b.value('./PropertyValue', 'varchar(max)') asd FROM [dnn].[dbo].[EventLog2] c cross apply ...
7
votes
2answers
1k views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
7
votes
3answers
609 views

Use 'default value' in table when NULL

I am building a config table and want one row to be the default value. For the example, I have a list table of all my databases and a msg table of my return values. In the msg table, I have one row ...
7
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 ...
7
votes
2answers
243 views

When running a script with several commands, how to find out where we're at?

I have some processes that require different steps to be taken before they start. For example: Disable check constraint on foreign key: select 'alter table '+fk.table_schema+'.'+fk.table_name +' ...
7
votes
4answers
5k 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 ...
7
votes
3answers
421 views

When are procedural queries absolutely necessary?

I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
7
votes
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
7
votes
3answers
269 views

sql server: updating fields on huge table in small chunks: how to get progress/status?

We have a very large (100million row) table, and we need to update a couple of fields on it. For log shipping, etc, we also, obviously, want to keep it to bite-size transactions. Will the below do ...
7
votes
1answer
660 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 ...
7
votes
1answer
4k 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 ...
7
votes
1answer
675 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
1answer
247 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 | ...
7
votes
3answers
7k 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
2answers
29k 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 ...
7
votes
1answer
5k 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
2answers
83 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value ...
7
votes
2answers
22k 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 ...
7
votes
1answer
724 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
2answers
103 views

Enforce Data integrity on an Email column in a database

I want to put a constraint on an email column like so: xxxx@xxxx.yyy Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc
7
votes
2answers
12k 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 ...
7
votes
1answer
104 views

Unique identifier field with a conditional

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique ...
7
votes
2answers
8k 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
121 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
97 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
7
votes
3answers
1k 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 ...
7
votes
2answers
3k views

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the ...
6
votes
4answers
361 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
2answers
219 views

How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

I downloaded the SQL Server First Aid Kit from Brent Ozar's website. When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in ...
6
votes
2answers
7k 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 ...
6
votes
3answers
281 views

The fastest way to get the most recent records [duplicate]

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
6
votes
3answers
210 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
6
votes
4answers
188 views

Multiple column concatenation

How to concatenate multiple columns in to a single row? For example: id name car 1 sam dodge 1 ram maserati 1 john benz 1 NULL mazda 2 kirk lexus 2 Jim ...