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

learn more… | top users | synonyms

1
vote
1answer
38 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
203 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
102 views

Long running simple query SQL Server 2008 R2 [on hold]

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 ...
1
vote
0answers
16 views

Update Only Rows With Shared/Similar Value [migrated]

OK - I have a simple table - below - what I am trying to do is to identify only those rows that have a shared value - so anything that has a shared "apc" value - DO x else Do Y CREATE TABLE #test ...
4
votes
1answer
62 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
163 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 ...
0
votes
0answers
19 views

Strange behavious 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
20 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
99 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 ...
3
votes
1answer
73 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 ...
-1
votes
1answer
33 views

Divide single column value into multiple column depending upon the bit value of another column [closed]

I have a table containing following fields. Id varchar(5), TranDate date, TranType bit, TranAmount decimal(15,3) My table looks like this: Id TranDate TranType TranAmount ...
1
vote
1answer
34 views

set date value based on other row values

Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem: I've got a table with the following ...
2
votes
1answer
60 views

TSQL- Column DataType Length

Can anyone please explain me the below one. I used the below query in getting the table columns info, but the max length seems to be x*2 of the actual col length. I mean if I have a col with datatype ...
0
votes
1answer
40 views

Join reorders results [duplicate]

Is there any particular reason why adding an additional join to an ordered table would reorder the result set? Using a left join on the existing result set table-valued function is similar to below ...
0
votes
1answer
51 views

How to deal with async calls to a stored procedure that updates data

I have asynchronous calls from Entity Framework to a stored procedure. The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls. The ...
1
vote
2answers
277 views

Adding Data to Multiple Tables Using Single Query [closed]

I want to add data to multiple tables using a single INSERT INTO query, can I do this? Please guide me and provide me syntax if this is possible.
3
votes
1answer
117 views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
0
votes
2answers
50 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
5
votes
2answers
154 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 ...
0
votes
1answer
38 views

Problem creating database using default mdf and ldf path

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into ...
-3
votes
2answers
87 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
0
votes
1answer
39 views

SQLCMD mode: environment variable syntax?

I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode. How do I use environment variables (e.g. %systemroot%) in the code? The ...
-2
votes
1answer
40 views

Add default constraint from another table

How to add a default constraint to a table such that the value that will be used as default will be from another table's columns? i have created a table MY_TBL in which having a column CURRENCY_CD ...
0
votes
3answers
158 views

How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this ...
3
votes
3answers
268 views

T-SQL Calculate average value for Duration Field

Can anyone please help me on this.. I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'. How do I calculate the average duration? I read some other ...
2
votes
1answer
52 views

SQL CMD Mode Issue

SET QUOTED_IDENTIFIER on declare @source_server_name sysname IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; create table #tmp( id int identity(1,1), server sysname ) insert into #tmp ...
0
votes
2answers
112 views

NULL value returned when concatenating a string in SQL while loop! [closed]

I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing? declare @o int = 10000000/1000000; declare @i int = 0; declare @s nvarchar(200); while @i ...
1
vote
1answer
43 views

How to use COALESCE with multiple rows and without preceding comma?

I'm trying to achieve the following: California | Los Angeles, San Francisco, Sacramento Florida | Jacksonville, Miami Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, ...
0
votes
0answers
29 views

“Not In” returns no rows all the time [duplicate]

Whats wrong with the following query. The first part of the select always return 0 rows. DECLARE @AgentID varchar(20), @StringValues varchar(max), @ContactKey int, @Note ...
-1
votes
1answer
34 views

combining two CTE in a single query

Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select ...
0
votes
2answers
71 views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( ...
0
votes
1answer
52 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
1
vote
2answers
103 views

Change DateTime format

How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below Modified Date 2007-09-01 02:00:00.000 2005-09-01 05:00:00.000 Output: Date 09-01-2007 09-01-2005
5
votes
5answers
1k 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 ...
0
votes
1answer
40 views

Right outer join seems to not work because of where clause filter

I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON ...
2
votes
1answer
53 views

Joining on same table without duplicating data in a field or using c#

I have the following table (a few fields omitted): CREATE TABLE [Device]( [DeviceId] [int] IDENTITY(1,1) NOT NULL, [AuthorizedDate] [datetime2](0) NULL, [DeauthorizedDate] [datetime2](0) ...
0
votes
1answer
36 views

Unable to check is schema is present first

I've created a SQL script that will release a process to numerous SQL Server instances so I want to check if a paticular schema is present before creating the schema. I'm using the below and it ...
1
vote
1answer
79 views

Given the same input, is the HASHBYTES function guaranteed to return the same output?

If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance? For example, will SELECT HASHBYTES('SHA1', ...
2
votes
1answer
51 views

Find Rows with the Minimum Date per Group

I have table like this and I need to remove duplicates and only leave the min date +-----+-------------+-------+------+-------+-------+-------+ | ID | Date | Alloc | Prod | Water | Tub | ...
2
votes
1answer
86 views

Queries for unique names from a site table using multiple criteria

So I'm just learning SQL but have wound up as a database manager. The software they use is overall easy but is very restrictive in the reports it can create, though it does allow custom SQL reports. ...
2
votes
2answers
49 views

Adding a varchar to a datetime column and dealing with nulls

I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX ...
1
vote
1answer
122 views

Does the Inserted table contain Identity values?

I have an issue with this AFTER INSERT trigger that I'm using for auditing: Cannot insert the value NULL into column 'TermID', table 'AuditTerms'; column does not allow nulls. INSERT fails. ...
3
votes
2answers
139 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
0
votes
1answer
69 views

How to restore Network backup to Network Path in SQL Server 2005?

I am trying to restore network backup file to network path.. is there any way to do it? I am using SQL Server 2005 and code.... RESTORE DATABASE DB_NAme FROM DISK = ...
0
votes
1answer
27 views

group by+where on calculated column [duplicate]

SELECT SUM(a)-SUM(b) AS c1, SUM(a) AS c2, SUM(b) AS c3 FROM tbldoc GROUP BY Cid WHERE c1<>0 If you take ( WHERE c1<>0 ) not error Thank you Answer
1
vote
2answers
56 views

How to restore a database then upgrade it in a single SQL script?

I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a ...
4
votes
3answers
267 views

Splitting a deck of cards, and returning the results as if they were shuffled once

Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a: select ...
1
vote
1answer
113 views

Include fields from a table function in a select

I'm running this query on SQL Server 2008 R2: SELECT orr.Rate, orr.ID, orr.UserKey, fn.NetRate FROM OngoingRate orr CROSS APPLY dbo.GetRateNetView(orr.UserKey) as fn WHERE ...
-4
votes
2answers
45 views

How can insert Data one Linked Server to Other Linked Server.

declare @sql nvarchar(max) set @sql='DECLARE @SourcePKTable TABLE (rowno int identity(1,1),myOldPK INT) IF EXISTS(SELECT Company_ID FROM [LServer2].[CMS].[dbo].[tbl_MasterCompany] WHERE Is_Sync IS ...
2
votes
2answers
100 views

Datetime column vs Date + Time columns

I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second. We are using SQL-Server 2012 Enterprise Edition. I have a question regarding breaking up a ...