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

learn more… | top users | synonyms (1)

24
votes
7answers
46k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
1
vote
0answers
60 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
0
votes
2answers
50 views

Format Phone Numbers In SQL Server

I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008? FORMAT([PhoneField],'###-###-####')
3
votes
3answers
62 views

Sum values from child tables and ignore equal rows

I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated ...
3
votes
0answers
64 views

Query with sp_executesql returns nothing while query without it returns the result [closed]

I'm using MS SQL Server 2008 R2 through ORM in .NET application. This ORM uses ADO.NET, so any query will be wrapped in exec sp_executesql. One of the generated queries returns nothing, but it ...
3
votes
1answer
27 views

Split Field Every 1K Characters

I have a field that can hold up to 3000 characters. I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, ...
5
votes
1answer
878 views

How do you get an average of job history times?

The following is a script I found online that has most of the elements of what I desire in the result set. The only thing missing is how I can get an average of the run times over a decided time ...
2
votes
1answer
48 views

Check 1 Table If No Data Check Second Table

I am passing into a stored procedure two variables @begindate & @enddate. I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate. I then need to ...
-1
votes
1answer
32 views

How can I automate the creation of SQL Server Agent Notifications on job failure?

There are no alerts setup on any of my SQL Server Agent jobs ( for failure,severity code etc.). Is there is a script to help or do I have to create an alert manually for every single job?
2
votes
2answers
940 views

Can I use a script to restore multiple SQL databases from .bak files?

I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp ...
11
votes
3answers
1k views

How does SQL Server store the information in Job Schedule Properties?

I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run. I want to know how this is all determined and stored. So where does it store the ...
0
votes
1answer
386 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 ...
2
votes
4answers
4k views

Null Or Multiple Parameters When Querying

I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as @Values VARCHAR(10) = NULL ...
0
votes
1answer
2k views

How to show NULL results from query?

i'm trying to create a procedure with filters for search. If the parameter is "NULL" the value of parameter will be column value; Here my problem: When my parameter and column value is NULL. I have ...
0
votes
1answer
233 views

Geography union via cursor returning null

I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is ...
-1
votes
1answer
982 views

Totals Column - Blank if Zero or NULL

A report currently computes a "totals" column like so: ,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) + ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0 THEN '' ...
12
votes
3answers
265 views

Create hierarchy of multiple levels where each node has a random number of children

I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. ...
6
votes
1answer
104 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
1
vote
1answer
73 views

How to increase performance of UPDATE of ~ 50.000.000 entries

We have a query that uses a temporary table to update certain columns in the database: CREATE TABLE #NEW_OLD ( NEW nvarchar(20), OLD nvarchar(20) ); INSERT INTO #NEW_OLD VALUES ...
2
votes
2answers
51 views

RIGHT() works in a SELECT but not in an UPDATE? [closed]

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
9
votes
5answers
2k views

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
4
votes
2answers
1k views

Why can't I view the SQL Server database created by a web application?

I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity ...
7
votes
2answers
243 views

When running a script with several commands, how to find out where we're at?

I have some processes that require different steps to be taken before they start. For example: Disable check constraint on foreign key: select 'alter table '+fk.table_schema+'.'+fk.table_name +' ...
7
votes
2answers
8k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
4
votes
2answers
108 views

Replace special characters in a column with space

I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid): SELECT columnA FROM tableA ...
4
votes
1answer
2k views

How to set identity_insert to on in SQL Server

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
-3
votes
1answer
33 views

Problem with INSERT INTO [closed]

I can't make INSERT INTO Booking work. My code: INSERT INTO Gaest (pasNr ,fuldNavn , adresse, tlf , email ) VALUES ('1234', 'Simon jensen', 'Heimdalsgade 6, 9000','6691612' , ...
1
vote
1answer
38 views

Update Int Field [closed]

Why is my table not getting updated? For example, if I run this statement alone, I have a value of 6 returned Select COUNT(*) from #madeupland where SOapproved IS NULL however, when it is run ...
0
votes
3answers
89 views

How to read and increment a number in text file using TSQL

I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6. Please help me with the T_SQL syntax. I have tried the following query to read the value but not able ...
6
votes
4answers
188 views

Multiple column concatenation

How to concatenate multiple columns in to a single row? For example: id name car 1 sam dodge 1 ram maserati 1 john benz 1 NULL mazda 2 kirk lexus 2 Jim ...
1
vote
1answer
38 views

Copy database to a remote server on daily basis

I need to replace the copy of my PROD db in a DEV system on a daily basis. The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked ...
2
votes
2answers
44 views

DayOfYear as per Indian fiscal year

I want to get sequence number according to the Indian fiscal year. I tried to write the function using SQL built-in function "DATEPART(DAYOFYEAR,'2015-04-01')" but at some point I am facing challenge ...
0
votes
1answer
44 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
92 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
49 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 ...
6
votes
2answers
220 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 ...
1
vote
3answers
66 views

How to retrieve the latest guid value after you have inserted a new row in realtime

Goal: Retrieve the latest guid value in real time after you have inserted the the value in the table Problem: Don't know how to do it Info: *You can only add a new vale that is address and zip code ...
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
30 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
80 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 ...
1
vote
1answer
53 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 ...
5
votes
1answer
11k 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 ...
2
votes
5answers
256 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 ...
0
votes
1answer
49 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
52 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 ...
288
votes
3answers
126k 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
354 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] ...
22
votes
3answers
10k 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 ...
8
votes
2answers
3k 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
3k 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 ...