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

learn more… | top users | synonyms

0
votes
1answer
13 views

DBCC DBREINDEX in MS SQL Server 2008

We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER ...
0
votes
0answers
19 views

Some records are missing while selecting from table

I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table. select WI.WORKITEM_ID as WORKITEMID ...
1
vote
0answers
29 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
1
vote
2answers
45 views

How can I Insert when not in table and when max timestamp T-SQL

I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that ...
0
votes
1answer
40 views

Which columns (and order) to choose for a clustered index to maximize query performance

In our database we have a table that looks more or less like the example below. In the past we always created the CLUSTERED INDEX on the ID column on the table. CREATE TABLE [Measurement] ( [ID] ...
0
votes
1answer
29 views

How to modify maintenance plan using T-SQL script in MS SQL Server 208

How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script? I want to change the schedule of a maintenance plan and change the path of the .bak file . All these changes should be ...
0
votes
0answers
15 views

SQL SERVER - Split Address Line [on hold]

I have a field that contains an address line that is like this Dr Robert Ruberry, West End Medical Practice, 38 Russell Street, South Brisbane 4101 I want to be able to select or create a view for ...
0
votes
1answer
34 views

Script works but Im not sure this is the best format

I have a survey table which contains mostly integer. I want to make it readable for end user so I use select statement as below: SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two' ...
0
votes
1answer
41 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
1
vote
0answers
19 views

Certification Test 70-461 [on hold]

I am a student at University of Houston-Downtown and looking to take the Database Administrator certification test sometime in October. I am hoping that someone has been recently been certified or ...
2
votes
0answers
31 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
35 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
9 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
49 views

About Criteria in T-SQL Statement [closed]

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
63 views

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

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
57 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
36 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 ...
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
850 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
46 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
130 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
438 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
249 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
29 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
46 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
79 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
46 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
54 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
51 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
44 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
111 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
232 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
55 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
155 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
32 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
69 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
53 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
46 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
70 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 ...