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

learn more… | top users | synonyms

2
votes
0answers
28 views

Code required to make column not nullable

I have a table where I mistakenly allowed NULL values in a column: CREATE TABLE EXAMPLE ( EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL, FOO_ID INT, CREATED_ON DATETIME DEFAULT ...
-1
votes
1answer
34 views

How to merge same values in a column and rows

How can i merge same Stock item value and get the sum of both value in the table This is Query: select Invoice_TBL.InvoiceNo ,Invoice_TBL.ClientNo ,Invoice_TBL.ClientFullName ...
0
votes
0answers
7 views

How to Customized the Where Statement based on UDT

Goal: Display customized data based on customized where statement from UDT. The UDT is a parameter inside of a stored procedure. Problem: A parameter from a stored procedure is @communication ...
-4
votes
2answers
46 views

About Criteria in T-SQL Statement [on hold]

Today the code is: select * from datatable where id is not null However, if I want to retrieve data of id I want to add another criteria in where statement I'm trying to improve the code below ...
5
votes
0answers
61 views

SQL Server - Best practices on debugging extremely complex stored procedures [on hold]

I recently inherited a MESS of a search. It's around 10,000 lines of code in the procs/functions alone. This search is aptly named the "Standard Search." It's a proc that calls about 6 other procs, ...
1
vote
2answers
52 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
34 views

Usage of COALESCE? [on hold]

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 ...
1
vote
3answers
66 views

Can you Schedule a job inside another job?

I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a ...
7
votes
1answer
833 views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
0
votes
3answers
43 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"> ...
0
votes
1answer
27 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 ...
3
votes
1answer
127 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 ...
2
votes
1answer
35 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
13 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
0answers
32 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
0answers
22 views

SQL Server - Count the number of times the contents of a specified field repeat in a table [migrated]

What's the best way to 'SELECT' a 'DISTINCT' list of a field from a table / view (with 'WHERE' criteria) and alongside that count the number of times that that field content repeats in the table / ...
10
votes
2answers
433 views

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, ...
3
votes
1answer
247 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
28 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
44 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
44 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 ( ...
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 ...
2
votes
2answers
78 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
1answer
42 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 ...
4
votes
1answer
32 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 ...
0
votes
1answer
51 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 ...
1
vote
1answer
50 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
43 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
4answers
107 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 ...
0
votes
2answers
35 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
231 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 ...
4
votes
1answer
51 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 ...
9
votes
2answers
145 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 ...
2
votes
1answer
30 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. ...
3
votes
2answers
67 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 ...
0
votes
2answers
52 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 ...
2
votes
1answer
45 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
30 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
66 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
45 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 ...
0
votes
1answer
64 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 ...
0
votes
0answers
55 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] >= ...
6
votes
2answers
111 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 ...
0
votes
0answers
46 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 ...
1
vote
1answer
50 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
46 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 ...
0
votes
2answers
44 views

How do I extract a repeating string pattern from multiple rows?

I have the following data: | ID | BadCol | +------+-----------------------------------------------+ | 1 | All of R489804 R489805 R489806 R489807 ...
2
votes
1answer
42 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. ...
1
vote
0answers
25 views

Can I Parse and/or include “And” in my SQL Statement for a full text index?

So I have a query that looks at a full text index like this: SELECT * FROM Table1 q INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search ON q.Key = search.[KEY] Now @Keywords ...