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

How to pull number code from strings buried in parentheses?

I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing... Data Samples: Widget (1234) Different Widget (123) Many ...
-2
votes
1answer
41 views

Usage of COALESCE? [closed]

Today: table abc a b c d ---- ---- ---- ---- 1 null 2 3 null null null 7 null 5 null 4 1 = e 7 = b 5 = f 3 = i 2 = w 4 = o The Goal is to retrieve this ...
2
votes
1answer
27 views

SELECTs in Database Change Log and Source Control

We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right, ...
1
vote
3answers
62 views

Load specific element from XML

I have the following xml: <root> <123 type="line"> <aa type="item"> <value>1</value> </aa> </123> <234 type="line"> <bb type="item"> ...
3
votes
1answer
156 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
0
votes
1answer
32 views

How to serialize on SQL server metadata?

I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata. The problem is how to ensure that only one of these reads and index creates executes from one ...
2
votes
3answers
712 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 ...
2
votes
1answer
73 views

When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query?

I have been troubleshooting an issue within a database that has some serious blocking issues. I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid. I ...
0
votes
0answers
25 views

Mass update taking to long [duplicate]

After searching through many posts I have come up with the code below to handle updating 200,000 to 2 million records daily. The problem is its still takes hours to complete, I have tried to narrow ...
0
votes
1answer
40 views

Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs

Is there any way to get total row count and size of result-set generated by a stored procedure execution? @@ROWCOUNT is a good option if its already added in each stored procedure but I just want ...
0
votes
1answer
30 views

Merging 2 queries together

I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before: tdate period staff ...
6
votes
3answers
2k views

Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other ...
0
votes
0answers
36 views

Deep cloning a hierarchical database structure

I am trying to clone a hierarchical structure in my database, and I almost have it working; however I can't seem to get the output clause to access a column from a select statement. This is generally ...
3
votes
1answer
273 views

Delete using a composite key

I am trying to wrap my head around creating a delete statement for a table with a composite key. I would like to create something like: DELETE FROM table_example1 WHERE COLUMN1, COLUMN2 ...
3
votes
1answer
56 views

xp_cmdshell in a transaction

I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch. Currently when ...
0
votes
1answer
57 views

Replacing a Cursor with a single Query possible?

I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor. The problem is I need to get items from a batch table based on a list of ...
1
vote
1answer
53 views

Having issues with a CTE in a Nested Join [closed]

I'm unfortunately dealing with someone else's code here, but needing a little push. What I have happening is that I have a CTE that is needed to run in a nested join. ...Start of CTE INNER JOIN ( ...
2
votes
2answers
94 views

String that contains letters representing a negative number

I would like for every string that has a letter on the end make it a negative value. <field> = '00000000014545p';--Varchar(15) <field> = '00000000012645x';--Varchar(15) <field> = ...
0
votes
0answers
38 views

Can a critical section cause deadlock in SQL Server? [duplicate]

We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work ...
0
votes
2answers
462 views

SQL Server compact- Query that return 2 constant columns even if query didn't return data

Please look at the following simple query: Select CustomerID From Customers c Where c.CustomerId = "1234" I want to write similar query that does the following: Always return 2 columns with ...
4
votes
1answer
88 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
0
votes
1answer
128 views

Fastest way to disable indices and drop constraints and rebuild SQL Server table

Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps ...
0
votes
1answer
97 views

How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL?

I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working ...
4
votes
1answer
40 views

Does NTILE work over floating point numbers?

I am trying to use NTILE on my data using a float data type in my PARTITION BY clause. Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through ...
1
vote
4answers
198 views

T-SQL insert value based on identity column

Is it possible to have a table with PK based on Identity columns? Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int ...
10
votes
2answers
277 views

Select all records, join with table A if join exists, table B if not

So here's my scenario: I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to ...
1
vote
1answer
61 views

How to Locate Specific Word

Goal: Locate specific word among tables in a database and display the name of the table and column in a list. Problem: There are TOO many tables to locate a specific word in the database. Info: ...
0
votes
1answer
57 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
1
vote
1answer
551 views

Selecting from an excel spreadsheet into SQL Server table

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express). So for the ...
0
votes
2answers
38 views

why the optimizer doesn't choose the optimal plan [closed]

I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan? Does any other reason/reasons exist?
2
votes
2answers
246 views

Half of a transaction completes, when the other half fails?

I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with. Admittedly I'm still ...
2
votes
1answer
1k views

Execute sp_WhoIsActive as a query

I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?
3
votes
2answers
144 views

How do I pass multiple statements into sqlcmd?

Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt. They ...
2
votes
1answer
36 views

How to generate an error when backing up to an existing file

I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file. By default, SQL Server seems to append to an existing file. ...
6
votes
2answers
190 views

How to move a fairly large tsql table from one server to another server?

I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box. They are at separate companies without an intranet linkage and obviously without linked ...
1
vote
2answers
49 views

Get time elapsed between log entries

At a company, fluids are prepared and held in tanks, following a preparation/recipe. The preparations move from tank to tank depending on needs. The tasks are logged in sequence into a table. What I ...
0
votes
2answers
84 views

How many records should be deleted when batch delete is used?

I am going to migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause, but need to have ...
1
vote
1answer
124 views

SQL Server Mirroring can't connect to mirror database

I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine. The error that keeps coming up is: Msg 1418, Level 16, State 1, Line 1 The ...
2
votes
1answer
142 views

SET CONTEXT_INFO for tracking specific SQL requests

I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state. I can see the machine name and ...
2
votes
1answer
51 views

Can't seem to understand why STUFF isn't working

I've got two tables that store questions and possible answers. The questions table can have multiple possible answers to it. I want to display the possible answers it can have per question.. For ...
0
votes
1answer
42 views

How to optimize STDistance execution?

I am creating temporary table during stored procedure execution with the following structure: [ID] BIGINT [Point] GEOGRAPHY the ID is not unique - there are about 200 records for each ID. ...
1
vote
1answer
99 views

SQL Server table and view show different values

I have a view that selects data from a table that is showing different data. The table is dbo.user, the view is dbo.users (don't judge, I inherited this system). The view is simply select * from ...
2
votes
1answer
47 views

Set based query to group data ordered data

I have a table of user action data and I would like to try and group it into 'sessions', so I could figure out how many times a user has logged on, how long they stay online, and how many actions they ...
1
vote
0answers
69 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
0answers
49 views

Rounding Casted Converting Error

The database I am working with has ntext datatypes which according to my understanding is evil. I used CAST to varchar to fix the issues in the below trimmed SQL Server query. Anyway, my question is ...
4
votes
2answers
973 views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
1
vote
1answer
200 views

Converting VARBINARY HEX to VARCHAR

I have a fun problem, I have data stored in SQL Server 2008 VARDATA column as Hex. I need to see what this translated to string is. I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a ...
-2
votes
2answers
116 views

UPDATE() Function (Insert and Update Triggers)

Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have ...
-6
votes
1answer
77 views

SQL Server Query Question

I get the following errors when I execute the below query Error messages: Msg 207, Level 16, State 1, Line 10 Invalid column name 'Name'. Msg 207, Level 16, State 1, Line 10 Invalid ...
2
votes
1answer
62 views

How to find the most common phrases in a varchar field

I have a table which contains support tickets. Each ticket has a title and many of them have common phrases in them (XXX cannot connect to YYY, XXX requires a new YYY, XXX requires upgrading, etc. ...