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
2answers
75 views

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

I have been working on making this query work more efficiently. I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored ...
1
vote
1answer
15 views

Connection refused when adding ApplicationIntent

Environment : AlwaysOn HA group, 2 nodes, 1 database, SQL2012, Server2012R2. Here is my .NET C# code : string connectionString = ...
-4
votes
2answers
55 views

sql server date issue [closed]

I have a select statement that when run on the live production server, returns mm-dd-yyyy regardless of what I do. this only happens though when selecting the date from an another aliased select. ...
0
votes
1answer
32 views

Update specific records of table based on subquery's output (sql server)

have this query which produce below output : select sec.site,sec.Name,sec.Freq from ( select Name from sec group by name having count(Freq) > 3)as dt inner join sec on dt.Name = sec.Name where ...
1
vote
2answers
96 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
0
votes
0answers
10 views

Curious Regarding filtering behaviour and performance of where condition

So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each ...
0
votes
0answers
42 views

LIKE or CHARINDEX to check String Contains a Substring [duplicate]

To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX]. I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use ...
2
votes
1answer
80 views

Converting Cursor based filtering logic to CTE based

I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition ...
0
votes
1answer
33 views

Simplifying query by adding Computed Column, performance impact?

The dev team came across the following query (might be incomplete; it's retyped from a screenshot) which had caused slowness: SELECT d.catalogno, d.itemid, SUM(lineamount+taxamount) AS Amount, ...
1
vote
1answer
232 views

Using TOP and getting different result sets

I am trying to write an efficient query for deleting chunks of data. To this end I hoped to avoid an index scan by using the primary key to get the oldest records. However I'm seeing some unexpected ...
-1
votes
2answers
91 views

Remove a character from a records enclosed between double quotes

Hi I 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 " ". ...
3
votes
1answer
114 views

When to use With Recompile

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc.. Sometimes there are cases that it may help, but OPTION(RECOMPILE) ...
1
vote
3answers
77 views

How to query for records based on most recent version

I have the following table structure ID, ORDERNUMBER, DATE, STATUS 1, 1, 1-1-01, proposal 2, 1, 1-2-01, proposal 3, 1, 1-3-01, active 4, 2, 1-1-02, proposal 5, 2, 1-2-02, cancelled 6, 3, 1-1-03, ...
-1
votes
0answers
24 views

Does a DELETE/UPDATE query based on a large SELECT query cause deadlocks and can it be improved?

I've noticed DELETE and UPDATE queries that are based upon a large SELECT statement such as NOT IN (SELECT ID FROM LARGE_TABLE) are often ending up with deadlocks. Is this causing deadlocks and if so ...
0
votes
0answers
31 views

Using CASE in CHECK Constraint [duplicate]

I cannot get SQL Server to parse my query as I tried to alter a table and add a constraint. like this: ALTER TABLE TaxVal ADD CONSTRAINT ch_taxVal CHECK (CASE WHEN Col1 IS NOT NULL THEN Col2 = 1 ELSE ...
-3
votes
2answers
107 views

Inserting data based on different conditons

Though , i put my efforts in making question easy to understand , but i still dnt know whats going wrong . Anyways , im trying to make it more compact I have below table now only : In column number ...
1
vote
2answers
180 views

How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012

I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows. Why is this happening? I have a scenario where I may have ...
2
votes
1answer
110 views

How to find number of days since beginning of fiscal month?

This relates to my question How to find XXth day of previous month in SQL server? The fiscal month starts on 26th of each month. I need to find out from any date: The date of the start of the ...
2
votes
0answers
55 views

Avoiding Functions() in the Where clause

Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query: (@p__linq__0 nvarchar(4000)) SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1] ...
3
votes
1answer
82 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 ...
2
votes
1answer
89 views

Finding rows with duplicate values

I have a one to many relationship between the Account table and a ScheduledPayment table. One account can have many payments but there should be a maximum of one payment per day. Although unlikely, ...
1
vote
1answer
58 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
2
votes
3answers
295 views

How to pull rows only from the weekends?

My table data is like this: ID Name salary date 1 a 100 1/08/2014 2 b 200 2/08/2014 . . . . 26 z 2600 26/8/2014 Now I want to return only rows from Saturday or ...
0
votes
2answers
142 views

Stored Procedure null parameter within where clause

I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no ...
0
votes
1answer
34 views

Backup/restore using OPENDATASOURCE OR OPENROWSET

Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.
0
votes
2answers
65 views

How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012

How I should escape the % character in params so my RAISERROR retun my message declare @msg varchar(max) = (SELECT ' Message with % ' AS MSG) if @msg is not null begin RAISERROR (@msg ,16,1); ...
0
votes
1answer
45 views

Difference between character_maximum_length and character_octet_length

Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here. I wanted to get some information about my Stored ...
1
vote
1answer
42 views

Group by alternative [closed]

I have a table , that has a generic set of actions for EVERY user. However if there is a user that needs to have a unique action, that replaces an existing action, then in my query, I want to ...
4
votes
0answers
52 views

Why is it @@SERVERNAME but not @@DBNAME? [closed]

I'm just curious: To get the server name, I use SELECT @@SERVERNAME but to get the current database name, I use SELECT DB_NAME() Technically, both seem to be scalar functions: @@SERVERNAME is ...
0
votes
1answer
137 views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
1answer
111 views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
1answer
112 views

How to reduce the run time of stored procedure by getting rid of cursors?

I have a task of reducing the run time of a stored procedure that runs for hours. Description of task: The cursor takes a set of values @Master and @Slave obtained from a query(5000+ rows), finds ...
0
votes
3answers
41 views

List all tables that a specific login has permissions [duplicate]

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
0
votes
1answer
33 views

Script to Add a Job to a SQL Server DB

I want to add a job to my DB to make a back up weekly but i want to do it from an script, i've serched and with what i found i made this: CREATE SCHEMA job go CREATE PROC BACKUPS @dir varchar(max) AS ...
1
vote
1answer
26 views

Enable Agent Database Mail - TSQL

I have several new servers that need Database Mail configured on them. I found the TechNet article describing how to add the profile and account via procedure calls, but is there a way to enable ...
0
votes
1answer
45 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
1
vote
1answer
99 views

HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
2
votes
3answers
165 views

Removing a string prefix

I have a string of numbers that I need to trim a portion from using TSQL. The string of numbers will always start with 101 then it will have a set of 0s and a set of random numbers. Example: ...
0
votes
1answer
40 views

Derive daily delta from total daily counts? Tsql

I have a table with columns: ID, tableName, totalRowCount, Date Each day I'm appending the number of rows in the relevant table. What I want to do is create a table which has the number of new ...
5
votes
1answer
185 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 ...
0
votes
1answer
33 views

Getting null block_st.text when querying for blocks

We are using the blocking query from this article which queries sys.dm_tran_locks. http://simplesqlserver.com/2013/05/21/blocking-capturing-and-monitoring/ The issue we've ran into is that often ...
1
vote
1answer
51 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
2
votes
2answers
245 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 ...
0
votes
2answers
129 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
4
votes
1answer
103 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
7
votes
3answers
217 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 ...
2
votes
1answer
57 views

Strange behaviours with SET options in SSMS

I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code: ensures that the SET options are as they should be creates a table ...
0
votes
1answer
29 views

Return Parents With Only One Child

In a Parent and Child relationship, I'm trying to return Parents that only have one Child and a where clause applied to the only child firstname like 'tom' t-sql pseudo: parents with only one child ...
2
votes
2answers
109 views

How I can force the TSQL-Developers to use not deprecated TSQL?

I want to avoid the using of deprecated code. Like the new feature Nr. 8 http://msdn.microsoft.com/en-us/library/ms178053.aspx I want to force them to use "WITH" for example, or a semicolon at the ...
4
votes
1answer
105 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 ...