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

learn more… | top users | synonyms (1)

0
votes
1answer
45 views

How can copy one column data of one table1 into other table2 column and other clumns table2 empty?

Here is my problem.I have two tables Stdd and result. stdd columns: id fname lname gender phonenum city result columns: physics chem math english urdu bio total id I want that in result table ...
4
votes
1answer
128 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
1
vote
1answer
50 views

Two If's And An Else [closed]

I am trying to run this t-sql query but I keep getting an error of Incorrect syntax near the keyword 'END' How should this be re-written in order to execute properly? What I want to achieve is ...
7
votes
2answers
293 views

How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

I downloaded the SQL Server First Aid Kit from Brent Ozar's website. When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in ...
12
votes
2answers
1k views

What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, ...
2
votes
1answer
34 views

Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use CAST(datediff(second, [start], [end])/3600.0 ...
2
votes
2answers
85 views

T-SQL SELECT use multiple indexes for no reason

I have a question about indexer and execution plans in T-SQL. My database is SQL Server 2008. I have a simple three-table db schema: The InverterData table is very large and partitioned ...
0
votes
1answer
90 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
2
votes
5answers
278 views

Order By DateTime With 00:00:00 Last

order by datetimecolumn orders a datetime column like this: datetimecolumn 2014-09-04 00:00:00 2014-09-04 00:00:00 2014-09-04 01:00:00 2014-09-05 00:00:00 2014-09-05 01:00:00 2014-09-05 02:00:00 ...
-1
votes
1answer
57 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
0
votes
2answers
57 views

Insert Data in seconds between 30 minutes time interval [duplicate]

I want to divide my time interval from the start time to end time. The elapsed time should be divided across 30 minute time intervals so that for this input 08:05:13 12:09:49 I should get ...
299
votes
3answers
135k 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?
8
votes
3answers
391 views

Updating a WHERE clause to check if a value is NOT in a separate table

I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al). The query is: SELECT DATENAME(DW, [AtDateTime]) AS [Day of Week] ...
23
votes
3answers
11k views

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
9
votes
2answers
4k views

Why should a CTE start with a semi-colon?

I was just looking at a post on SO (http://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table) where Aaron Bertrand proposes using a CTE instead of a numbers table, which ...
5
votes
1answer
4k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
5
votes
3answers
7k views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
7
votes
1answer
112 views

Unique identifier field with a conditional

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique ...
1
vote
2answers
85 views

How to keep row ordering with DISTINCT command or how to add row number to cross apply

I have problem that after distinct, order direction of Variable column change. Here is example Data Source DECLARE @Table TABLE ( FirstName NVARCHAR(255), LastName NVARCHAR(255), ...
4
votes
2answers
77 views

Update fields that contains certain prefix by lookup table

I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly ...
4
votes
2answers
74 views

Are temp tables automatically created when referencing to them?

I can across the following script and I am trying to understand it. select columnA, columnB, columnC into #tmpTable from tableA The #tmpTable is referenced after that query, but not before it. ...
6
votes
1answer
92 views

Query tuning issues

I'm in trouble trying to tune this following query. I used to be 100% focused in the infrastructure side of the stuff, but since August this year I'm working for a processing card company that is ...
1
vote
2answers
79 views

Problem executing multiple procedures [closed]

I'm really new to SQL Server development and I learn everyday. Today I encountered a problem while executing multiple stored procedures in a query. I saw only one of both start. After a long research ...
3
votes
1answer
52 views

Easiest way to find the larger version number?

I have a table that stores version information in multi-dotted strings, ie '4.1.345.08', '5.0.1.100', etc. I am looking for the quickest way to find out which is larger. Is there a faster, or easier ...
4
votes
1answer
68 views

NOT IN (together with join) query

I have two tables : Part, OrderLine: Part (PartNum PK, PartDesc, Price) OrderLine (OrderNum PK, PartNum PK, QtyOrdered) And I want to find out the parts that were not ordered. I think I have a ...
1
vote
0answers
28 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
-4
votes
1answer
46 views

Why all ranking is 1 for this query [closed]

Declare @ranking_term char set @ranking_term ='Amount' select case (@ranking_term)when'Amount' then ( select Dense_rank() over (order by Amount desc) ) else( select ...
7
votes
2answers
148 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value ...
0
votes
1answer
34 views

Why the result is ranked by both amount and date instead of amount only? [duplicate]

Declare @ranking_term char set @ranking_term ='Amount' select Dense_rank() over (order by case(@ranking_term) when 'Amount' then 1 else 0 end, amount, case(@ranking_term) when ...
5
votes
4answers
2k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
4
votes
1answer
42 views

Is is possible to combine local variables in a case statement?

DECLARE @CalcQuery varchar(340) SET @CalcQuery = (SELECT sum(T0.[LineTotal]) FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [@SES_SSNSBRG] T2 ON ...
1
vote
2answers
398 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
3
votes
2answers
94 views

Data extraction with multiple delimiters?

I have a legacy data source column that is delimitted by semicolons and commas. The first semicolon indicates the last name, the second indicates the first and middle name (or initials), and the last ...
0
votes
1answer
71 views

Update if not exists statement

If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict? Here's a simplified ...
5
votes
3answers
132 views

What are the SQL Server 2012 versions of FIRST() and LAST()?

I have a table with a value column. I want to calculate the last row minus the first row, as shown here: id value 1 10 2 45 3 65 4 95 . . . . ...
1
vote
2answers
2k views

Options for keeping two SQL Server Compact databases in sync?

Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL ...
2
votes
3answers
120 views

Explicit transaction left open after try/catch

I recently wrote a T-SQL script to perform some updates and inserts on 3 different tables. I wanted it done in a single transaction, so I read up on the Microsoft documentation on how to use explicit ...
5
votes
2answers
226 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
7
votes
2answers
105 views

Enforce Data integrity on an Email column in a database

I want to put a constraint on an email column like so: xxxx@xxxx.yyy Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc
0
votes
1answer
37 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
0
votes
0answers
30 views

The best way to raise error from mass insert query

I have a following sproc: CREATE TABLE Table1 (val INT) GO CREATE TABLE Table2 (val INT) GO ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (val > 0 ) GO INSERT INTO Table2 SELECT 1 UNION ALL SELECT ...
2
votes
1answer
85 views

Updating table based on conditions (overlapping dates)

Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update ...
3
votes
1answer
67 views

Unpivot with rows converted to columns as well as columns converted to rows

I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and ...
1
vote
0answers
103 views

xp_delete_file not working

I am running a query window under my own account and the following command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02' ...
4
votes
2answers
219 views

Executing the same SQL statement for different tables

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table. UPDATE desttable SET desttable.reportarea = esz2reportarea.reportarea ...
-7
votes
1answer
47 views

Need Immediate Help To Develope T-SQL

Hi DBAs, I want to Develop a T-SQL For Fetch data from following Table. But Condition is : I want to select only those data which status has all three status. suppose for id 1 : It has Only two ...
1
vote
1answer
3k views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
3
votes
1answer
28 views

Crosstab Pivot or not?

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below: As you can see, there is an invoice ref number along with a date, and ...
1
vote
0answers
73 views

Powershell To Run SQL Case Update [closed]

This is my syntax which throws an error of incorrect syntax near abcdefg. Can someone who is more experienced in the world of powershell take a gander and let me know how to remedy my issue? ...
1
vote
1answer
426 views

Is it worth time converting Access query to T-SQL query

Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions. I had a database built in ...