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

learn more… | top users | synonyms (1)

1
vote
1answer
41 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
0
votes
1answer
82 views

Create New Database user to login to SQL Server Management Studio

Please see the T-SQL below: create database db; go use [db] create login [standard] with password ='dkdkdkqajHGgbh56' CREATE USER [standard] FROM LOGIN [standard]; exec sp_addrolemember 'db_owner',...
4
votes
2answers
57 views

Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
1
vote
1answer
33 views

SQL Server - Analyzing Multiple Stored Procedure Statistics

I'm trying to analyze 1000+ stored procedures and I need to find out the: Elapsed Time in Sec (AVG based on # of iterations) CPU (AVG based on # of iterations) Logical Reads At first I'm using SQL ...
1
vote
3answers
80 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
0
votes
1answer
79 views

Extract Date Portion Only From DateTime Field

In SQL Server 2008 I use this to take out only the date piece Cast(field as date). For example, if I use the below garbage DDL Create Table #Bbb ( id int ,datetime1 datetime ) ...
0
votes
1answer
37 views

What is the request_id in sys.dm_exec_requests and how does it change?

According to MSDN, the request_id in sys.dm_exec_requests is the "ID of the request. Unique in the context of the session. Is not nullable." It always appears to be 0, from casual observation....
0
votes
1answer
154 views

AlwaysON Availability group query very slow

I am running below tsql to find the secondary AlwaysON servers from the primary replica : set transaction isolation level read uncommitted set statistics time, io on SELECT ISNULL(agstates....
1
vote
1answer
85 views

SSIS push and pull performance

After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS? For example, I have 2 SQL Servers (A and B) and SSIS ...
2
votes
2answers
170 views

Adding a new primary key ID column to an existing table

I want to add a new column to a table. The table is already populated with data. I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it. I ...
0
votes
2answers
79 views

SUM() In SQL Server

Running SQL Server 2008 I can not wrap my mind around why these two queries, which are identical except for the first one has COALESCE() wrapped around the entire statement and the second one has ...
3
votes
1answer
281 views

Adding Where Clause Skews Data

This is the result set that I get returned using my query: empname itemssold JJ 14 But this is my desired result set: empname itemssold AA 0 BB 0 CC 0 DD 0 JJ 14 RR ...
1
vote
1answer
71 views

Trigger Not Updating

Do I have an issue with my syntax? I created this trigger as sometimes the sales personnel forget to input the sale date (I Know it should be automatically captured which is an update in progress) ...
0
votes
1answer
26 views

Scripting execute permissions on stored procedure

My goal is to write a script which: Creates a stored procedure Grants execution permissions to a particular user on that new stored procedure On this SO question the recommendation is to add GRANT ...
4
votes
2answers
173 views

DayOfYear as per Indian fiscal year

I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function: DATEPART(DAYOFYEAR,'2015-04-01') but I am facing a challenge to get day ...
1
vote
1answer
84 views

SQL Data in form of Date+Time bulk insert into one column

Background of problem: To insert 2 seperated fields of data (from a big flat file document of data) with bulk insert. Columns in question that i want to get data from is in its current form in date ...
0
votes
1answer
54 views

What is the meaning of & in where clause

What does & indicate in below query? What is the meaning of it? select id, name from test where num & 1 = 1;
1
vote
2answers
111 views

Can I create a new user in MS SQL from windows and provide the SID

I am trying migrate databases and along with the database I am trying to also migrate the users without having to re-map the users. I was trying to recreate the windows user with the old server SID. ...
1
vote
1answer
49 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
1
vote
2answers
34 views

How to return all the unique column values on a per column basis for all metadata tables?

assume multiple tables of various sizes table one col one, col two, col three,... a,d,j a,e,k a,e,m b,e,o c,e,o d,e,o f,e,o return (preferably in this format) col one, col two, col three a,d,j b,...
1
vote
1answer
47 views

Find if column exist in certain tables

I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details. The tricky part is ...
4
votes
4answers
210 views

Validate three digits before decimal and after decimal point six digits

I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed. Can anyone help me to validate this in ...
0
votes
2answers
294 views

How do I extract a repeating string pattern from multiple rows?

I have the following data: | ID | BadCol | +------+-----------------------------------------------+ | 1 | All of R489804 R489805 R489806 R489807 ...
1
vote
0answers
37 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
1
vote
0answers
34 views

Greatest N per Group in SQL Server [duplicate]

I want to find classical Greatest N per Group. I have two methods to solve this problem Dense_rank Over() method Min Over() method Both work flawlessly. Now I want to find which one is better ...
-1
votes
1answer
76 views

Impact of WHERE Clause on Table-Valued Function and query performance

I have taken over a legacy application that has had many performance issues. One I found is on a table valued function. The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has ...
0
votes
2answers
52 views

Inline SQL To Set Fully Qualified Name [closed]

Why does my variable @FQN not set accordingly? Is this a problem with my syntax? A Problem with using a cursor? What should I do so that my variable @FQN sets appropriately? This is what my print ...
0
votes
2answers
44 views

Restore without specifying location for each file

I am working with a database that has a dynamic filegroup structure. The number of filegroups and filegroup files can and do change. Is there a way to specify one data and one log folder in a script ...
3
votes
1answer
50 views

SQL Server 2012 Client Statistics Incorrect

When I run my query, I get the following Client Statistics Results :- Number of SELECT statements = 2 The query being ran is only 1 SELECT statement :- SELECT TOP 1000000 * FROM [DATABASE].[SCHEMA]...
0
votes
1answer
43 views

Get Sale Info By Employee

I need a way to get detailed sale information for each employee in the StoreInfo table. Each employeename in that table has it's own table with data structure mirroring the table information below. ...
0
votes
1answer
125 views

Delete Works in one Stored Procedure but not another

I've built an app that uses stored procedures to insert into, update, and delete rows from tables in my database. All domain users have been given execute permissions on these procedures, all ...
3
votes
1answer
115 views

How to compare current record with previous records

Is there a way to group by and compare current record to previous records when reading from a table. My table looks something like below. ID PASSED LEVEL CREATEDATE 100 1 2 ...
1
vote
1answer
87 views

column alias in dynamic pivot [duplicate]

I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the ...
1
vote
0answers
34 views

How to properly re-run a deadlocked trasaction in TSQL

In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim. EX: "Transaction (Process ID 118) was deadlocked ...
2
votes
1answer
58 views

Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table

For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer. My issue that I am having with my query is it flags the first 3 dates returned regardless of if ...
0
votes
1answer
543 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
4answers
216 views

Script to create multiple databases

I need to create 90 databases. Something like this. Do you have any idea? My script: declare @db_name nvarchar(100) set @db_name = 'name'; CREATE DATABASE @db_name CONTAINMENT = NONE ON PRIMARY ...
0
votes
3answers
193 views

how to convert this varchar to datetime format?

I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687). ALTER ...
3
votes
1answer
37 views

Display Data Using Pivot

Is a pivot the best way for me to display this data? I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this id uuid ...
3
votes
2answers
96 views

Return values from Left Table Only Once

This is my table structure and a sample query. However, my query returns these results: ID event1 Exists 1 cycle No 1 cycle Yes 2 run Yes 3 walk No As you can see ID 1 is ...
0
votes
0answers
50 views

Issues with the @query parameter in sp_send_dbmail

When I use the following: select * from table A where repID in ( select repID from table B where id = 5 ) as the string I'm assigning to the @Query parameter. ...
0
votes
1answer
27 views

Alter Time of DateTime Field

I am needing to alter the time piece of a datetime field. I tried to use this syntax, but it gives me an error of Incorrect syntax near the keyword as What syntax can I use to alter the end ...
0
votes
1answer
81 views

Create Calculated Fields from Distinct Values in Second Table

I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code ...
16
votes
1answer
2k views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
2
votes
2answers
16k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | 10/10/...
0
votes
2answers
55 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
19
votes
6answers
3k 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 * ...
1
vote
1answer
57 views

Concatenate data in a single row [duplicate]

I have the following table: TicketID Type Code 44899B82-41C9-4020-9C2B-007C6000D283 ARTI 22150012 44899B82-41C9-4020-9C2B-007C6000D283 ...
4
votes
2answers
204 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
8
votes
2answers
2k 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 ...