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

learn more… | top users | synonyms (1)

11
votes
5answers
4k views

Does it make sense to use SQL Server's bracket notation in hand written code?

Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything. When I first saw it, I though wow a reincarnation of the somewhat ...
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 ...
3
votes
2answers
2k views

Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. ...
3
votes
1answer
310 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
3
votes
2answers
1k views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
15
votes
4answers
34k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
12
votes
3answers
1k views

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and am still confused on it. According to MSDN: Specifies that the query is optimized for fast ...
10
votes
5answers
2k views

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
10
votes
1answer
1k views

Generating large strings for test data

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
7
votes
2answers
2k 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
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
1answer
120 views

Is it possible to add a certificate to a database role?

I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using: CREATE ...
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].[...
4
votes
1answer
15k 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
7k views

Restoring a SQL Server 2012 database in high availability

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? I'...
3
votes
2answers
124 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
1
vote
1answer
2k views

Applying Quotes Across Multiple Lines

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk. Is there a similar keyboard function that applies single quotes across ...
1
vote
2answers
696 views

Can I send a string over TCP using T-SQL?

I've set up some alerts, based on this article Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
11
votes
2answers
6k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
7
votes
2answers
26k 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
730 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 ...
6
votes
4answers
600 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
4k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
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
354 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
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 ...
4
votes
1answer
174 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
4
votes
2answers
82 views

Strange looking where clause in sql server

I've found fa strange looking code in T-SQL. Here is the strange looking part: WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition)) The query works well, but what is the point ...
4
votes
2answers
12k 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 ...
3
votes
1answer
2k views

Bitwise operators affecting performance

I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the ...
3
votes
1answer
734 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
3
votes
2answers
2k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
3
votes
1answer
336 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
3answers
516 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
3
votes
1answer
88 views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
3
votes
3answers
851 views

t-sql - combinatorics

I'm trying to find all possible character combinations in a variable length string. For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--' I think that essentially I need ...
2
votes
3answers
260 views

Query databases on different servers

How can I create a query that will find all the databases on different servers? I have four servers, PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or ...
2
votes
1answer
193 views

How to improve this queries performance by indexing properly?

Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek. DECLARE @now DateTime; DECLARE @...
2
votes
2answers
2k views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
2
votes
2answers
223 views

Updating indexes and stats script

I am having the following errors with the script below Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47 Incorrect syntax near the keyword ...
2
votes
2answers
11k views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
2
votes
2answers
844 views

Pull every other row, starting at 0, and concatenate them together

I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other ...
2
votes
2answers
106 views

SQL Server changes execution plan

I have SQL code that is executing slow DECLARE @ccode varchar(500) SET @ccode = '%NIP%' SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode Why is SQL ...
1
vote
1answer
97 views

SQL Server lock_escalation flag on ALTER TABLE — can I really restructure without a table lock?

SQL Server 2014 Standard Edition In my situation, I have a huge table (100 million+ rows), and need to: add column to it set default value in that column I see in https://technet.microsoft.com/en-...
1
vote
1answer
43 views

How to synonymize a database name?

Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where: DBApp_DEV shall access ...
1
vote
2answers
892 views

How to link each column in sql?

I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
1
vote
1answer
142 views

SQL Server 2008 and constant expressions

If I have the following scenario, would Replace be evaluated a single time? DECLARE @text nvarchar(32) = 'This is a test field' SELECT textField FROM Table WHERE Id = @idICareAbout AND textField = ...
1
vote
3answers
3k views

Extracting an an integer value from an xml column [closed]

I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will ...
0
votes
1answer
126 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...