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

learn more… | top users | synonyms

0
votes
1answer
20 views

A query for merging date intervals

I have a simple table with 3 columns: (cid, from, to) -> (int, datetime, datetime) The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data ...
224
votes
4answers
73k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
1
vote
2answers
58 views

Query for orders that contain a product without using sub-query

Order ----- OrderId OrderDetail ----------- OrderDetailId OrderId ProductId How do I write a query that returns all orders that contain a specific product without using a sub-query? I don't want ...
1
vote
1answer
103 views

Count rows in dataset SSRS2012

I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression : =CountRows("DataSet1") But I have the following error message: "The expression ...
8
votes
1answer
188 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 ...
0
votes
0answers
26 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
2
votes
1answer
72 views

SET CONTEXT_INFO for tracking specific SQL requests

I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state. I can see the machine name and ...
4
votes
2answers
284 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 ...
0
votes
0answers
48 views

TSQL LAG equivalent

I am trying to understand how lag works, by converting the following code back to using self-join sql code without using lag. https://msdn.microsoft.com/en-us/library/hh231256.aspx USE ...
6
votes
1answer
190 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 | ...
0
votes
1answer
509 views

Selecting from an excel spreadsheet into SQL Server table

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express). So for the ...
3
votes
3answers
75 views

I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches

I have the following basic dataset in MS SQL Server 2008R2: create table #temptable ( Source varchar(255), PollTime datetime, Value numeric(20,2) ) insert into #temptable( Source, ...
2
votes
1answer
105 views

T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

My sample database (in fact a CTE statement data) looks like this: eventdate val 2012-03-23 3965 2012-03-26 3979 2012-03-27 3974 2012-03-28 3965 2012-03-29 3967 2012-03-30 3959 2012-04-02 ...
-1
votes
2answers
742 views

Remove a character from a records enclosed between double quotes

I have a table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What I want is to remove every comma (,) that is enclosed between double quotes ...
1
vote
3answers
172 views

How to create a SQL trigger to execute a stored procedure when a table is updated

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an ...
2
votes
1answer
67 views

Optimise select distinct from large table

I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records come down to 145,878,852. The ...
-4
votes
1answer
40 views

Who is depends on specific column? [closed]

I want to alter column table from varchar X to Y. And for that I need to know who is depend on this column . Any suggestion?
3
votes
1answer
424 views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
1
vote
1answer
262 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
4
votes
1answer
42 views

Null Values in a CASE statement

I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to ...
-1
votes
0answers
38 views

Converting Script from Teradata to SQL Server

I am currently working on moving a database, and all of its related scripts, off of Teradata 13 and into SQL Server 2012, and am having an issue with just one of the scripts. In the script, the ...
1
vote
1answer
32 views

Select all most recent records

I have this two tables (and their main fields): Target: Message: Id IdMessage Id IdMain IdReply DatePosted ================ ...
0
votes
3answers
96 views

A more efficient way to write multiple CASE statements [closed]

SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END, CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( ...
1
vote
2answers
69 views

SQL Server query

All machines should have three groups(grp1,grp2,grp3) but some have two groups and some might have all three and some might have 1 or zero. I have data like this, I have two columns: MachineName ...
0
votes
2answers
35 views

How can I move backups location?

I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql? I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that ...
2
votes
1answer
480 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
2
votes
1answer
53 views

T-SQL query to find backup folder for specific maintenance plan

I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012. Is there any way to query the maintenance plans for databases, ...
-1
votes
1answer
73 views

use variable as column name in where clause in sql

I want to use a variable as column name in my SQL query. Problem: I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function. What are other possible ...
0
votes
0answers
22 views

Adding lot of new role assignment on different folders

On our local SSRS we are currently adding a big quantity of different users on different folders. The usual steps are like the ones detailed here SQL Server Reporting Services 2012 Permissions. Is ...
1
vote
1answer
216 views

Attempting to Pivot a column of grades and coming up with NULL values

I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything. Here's the source data: ...
2
votes
1answer
50 views

Stored procedure with dynamic sql. Is it isolation safe by default?

I would appreciate confirmation on the answer I found digging through T-SQL's documentation. I have a stored procedure that references a view twice. Once in the procedure and then a second time when ...
4
votes
3answers
15k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
0
votes
1answer
52 views

SQL weight multiplication

Hi all i have two columns in a table : +--------------------+----------------+ | Units/Pk | PackWeight/Vol | +--------------------+----------------+ | 1.0000000000000000 | 150g | ...
0
votes
1answer
47 views

How to get best mark for each Student [closed]

I can't find a solution although it should be easy one. I have some students and all I need is to get the highest mark of each one in Math. TableName: Students. Columns: Name Mark John 90 John 84 ...
0
votes
0answers
36 views

Issues with Optimizing TSQL Script

I have a temp table that has a SecurityCode column. This SecurityCode column gets updated based off value from a CrossReference table. The value selected from the CrossReference table depends on how ...
3
votes
1answer
202 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
2
votes
1answer
71 views

If “large value types out of row” table option is enabled, the table size is increased

I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table. A colleague of me told me if I enabled it, each field is stored ...
3
votes
0answers
67 views

How to format T-SQL identifiers in camel case

A number of tools exist which are designed to facilitate formatting the T-SQL in an inherited script or stored procedure to match one's personal preferences. One feature I would like to see in such ...
1
vote
1answer
77 views

Table size is not changed after new column is added

I am trying to check how the table size is going to be increased after adding new column with default values. I use the following default values: NULL, ''(empty string) and some text(some random ...
0
votes
1answer
67 views

How to increase the performance of this 3 minute reporting query

We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ...
0
votes
1answer
31 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Lagitude and Longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
0
votes
0answers
37 views

delete millions of rows from multiple tables

Hi we have to delete between 40 and 55% of records from several tables that have between 40 million and 100 million rows. Is it better to copy the rows we want to keep to a new table, drop the source ...
6
votes
2answers
19k 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 ...
4
votes
2answers
88 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 ...
1
vote
2answers
86 views

Stored procedure - problem with merge - Insert

I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct? Any help is appreciated. Here is ...
1
vote
2answers
138 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
0
votes
1answer
42 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
4
votes
4answers
14k views

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

Does the SQL Server 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 set of conditions, does that ...
1
vote
2answers
71 views

T-SQL — Why do ALTER and UPDATE statement only run when separated into their own batch?

I have update and alter statements that are only running when they are put into they're own batch. Below is my shortened version. It'd probably be a good idea to skip the code and read my question ...
4
votes
1answer
208 views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL db. If the row already exists in the table, we update that ...