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
3answers
142 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
6
votes
5answers
4k 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
54 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
69 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
45 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
197 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
67 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
100 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
68 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
273 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
214 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
170 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
35 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
92 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
297 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
134 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
127 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
159 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 ...
1
vote
2answers
2k views

deleting large volumes of rows from table efficiently in SQL Server [duplicate]

I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system ...
2
votes
1answer
331 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
0
votes
3answers
114 views

How to improve this query performance by indexing properly?

I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek? DECLARE @now DateTime; DECLARE ...
0
votes
2answers
148 views

Remove a period if it is the first character in a varchar column

I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first ...
3
votes
1answer
98 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
0
votes
0answers
24 views

Trying to find unused stored procedures, but dm_exec_procedure_stats seems to be resetting [duplicate]

I need to identify stored procedures that are no longer used by our application (~2,000 sprocs, duplicated in ~1,000 databases). To do this, I'm querying dm_exec_procedure_stats, which initially ...
24
votes
1answer
837 views

Why is the speed of executing statements dependent on the network connection?

It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it ...
1
vote
1answer
267 views

What causes 'Unknown token received from SQL Server'? [closed]

I have a SQL Server 2005 box with a classic ASP front end (IIS6) calling a stored procedure that uses dynamic SQL. It works except for a single day of data that somehow throws this error even though ...
-1
votes
1answer
106 views

Find day values in bigint date?

This query is working good. select DATENAME(DW,getdate()) AS [Weekday] My Problem is suppose I give input values like select DATENAME(DW,InvoiceDate) AS [Weekday] from store; is not working in ...
2
votes
1answer
154 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
156 views

How do I safely drop a list of tables, when i have the table names listed in a column?

Assuming I have a database column containing table names, how do I safely delete all of the tables it lists? The tables themselves are fairly simple - they may have indeces, but definitely don't ...
3
votes
4answers
213 views

How to find duration in a dataset

I have a dataset of the following structure: Target, PollTime, Value ------------------------ 1. A, 2013-12-13 13:31:44.000, 15.00 2. A, 2013-12-13 13:32:44.000, 16.00 3. A, 2013-12-13 13:33:44.000, ...
4
votes
1answer
174 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
11
votes
3answers
652 views

Get streak count and streak type from win-loss-tie data

I made a SQL Fiddle for this question if that makes things easier for anyone. I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
0
votes
1answer
106 views

SQL Select query to include each row a number of times

This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question: I need to do do a projection on fields in a ...
0
votes
1answer
365 views

Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility. I want to text query a single large table in a SQL Server ...
0
votes
1answer
174 views

How to create a versioning DDL/DML change script for SQL Server?

I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the ...
10
votes
1answer
777 views

How can I make a query take a specific amount of time?

I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have ...
5
votes
1answer
366 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
0
votes
1answer
59 views

Is a single batch request equivalent to a single tsql request?

I'm looking into some performance metrics and every recommendation includes monitoring Batch Requests/sec. But, maybe a silly question, does a singe batch request correspond to exactly one tsql ...
5
votes
2answers
155 views

'+' operator with one operand!

How does '+' operator behave in following statement? select + 'taco'; --Result is 'taco' Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
3
votes
1answer
71 views

Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in ...
1
vote
2answers
224 views

Generic script for stored procedure rollback

We're trying to switch to feature-driven development in my organization. We're planning on using a new branch on our source control for each feature and merge when development is complete. I think it ...
1
vote
2answers
302 views

Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts: DECLARE @database_id int, @database_name nvarchar(100); DECLARE ...
2
votes
2answers
336 views

What is the best way to get all data for a date range, plus the last event just before the range?

For this question assume the following: There is a table of events [Event] There is a table of employees [Employee] There is a table that relates the two [EventEmployee] (EventID + EmployeeID) Each ...
0
votes
0answers
445 views

Row_Number() not working with OVER PARTITION BY

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field: [CNumber] [PNumber] [date] [FullName] [Type] [P] [S] ...
5
votes
1answer
91 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
1
vote
1answer
81 views

How do I solidify my SQL Server / T-SQL Knowledge? [closed]

I am a C# developer and not a database admin, but I have been using SQL Server for application developnment for over 4 years now and was really interested in getting a more complete understanding, and ...
1
vote
1answer
298 views

Return every instance of a string contains search

I have this sample table and data CREATE TABLE [dbo].[SampleTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [TextColumn] [nvarchar](1000) NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY ...
1
vote
1answer
79 views

Using CHECKSUM on where clause

I'm going through a long stored procedure and I found this, as part of a long query: SELECT .... FROM --- WHERE condition AND CHECKSUM(r.roomprovider_id) <> ...
0
votes
0answers
85 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
1
vote
2answers
317 views

SQL Server select columns which contain data

I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database ...