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

learn more… | top users | synonyms (1)

1
vote
1answer
107 views

Create index clustered X NonClustered with include

I have a table like : create table x (a varchar(20), b varchar(30), c varchar(30), primary key clustered(a asc)) so I'm wondering if I create an index like: Create nonclustered index test on x(a) ...
3
votes
1answer
389 views

The multi-part identifier “d.dept_name” could not be bound

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp ...
1
vote
2answers
123 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
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. ...
1
vote
2answers
59 views

Find highest sold item name for each date

I have three tables table 1: items (int,itemName) table 2: orders(order id,oDate) table 3: orderDetails(orderId, itemId, itemQnt) Here is the complete schema with some demo values What is the ...
0
votes
1answer
61 views

Simply inserting to temp table and failing

Can anyone tell me why this won't work for me? CREATE TABLE #UndistributedCmds ( pendingcmdcount int, estimatedprocesstime INT ) INSERT INTO #UndistributedCmds EXEC ...
4
votes
2answers
152 views

Sort a varchar type column

In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort ...
1
vote
1answer
66 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
1
vote
0answers
15 views

Opponent Winrate Over another opponent sql

I am trying to find the most efficient way to build a counter warehouse. shortened the tables just to needed info for question Match Table MatchId, version, type MatchParticipants matchId, ...
1
vote
1answer
114 views

LEFT OUTER JOIN versus WHERE IS NULL

For some irrelevant reasons it's hard for me to get LEFT OUTER JOINS working with multiple ON clauses. Since simply moving these clauses to the WHERE statement results in different result sets, I add ...
5
votes
1answer
196 views

Incorrect syntax near 'role' with generated script

I've recently had to recreate a user several times. To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window. However, after ...
4
votes
2answers
370 views

Exporting results to XML

I am trying to create an XML file from a SQL query. The out put should look like the image below Where the tag BranchID appears again as SubParentBranchID if there are more than one SubBranchID ...
8
votes
2answers
143 views

SQL Server 2008: sequence which restarts daily

I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are ...
0
votes
1answer
73 views

Stored Procedure produce data for a range of dates

I have this table: PRJ_ID | START | FINISH xxxx | 2015-01-01 | 2016-01-01 I have two separates date start_range and end_range. I would like to produce the following output using a stored ...
2
votes
3answers
287 views

Student who got MAX marks in subject

I am facing one issue in getting student name who scored MAX marks in each subject. Here are my tables below First one is Student table having STUD_ID , STUD_NAME & Second one is MARKS ...
1
vote
1answer
45 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
1
vote
1answer
217 views

SQL Server stored procedure producing data related to months between two dates [duplicate]

I'm stuck on the following problem. I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is: ...
0
votes
1answer
24 views

Put in the wrong data type for pk, cannot change because it is FK for another table [closed]

I'm trying to change the PK Department.DeptId to char(8), however it is an FK in employee. Doing two alters in 1 query throws an error for the first statement. Changing it in the edit window does the ...
0
votes
0answers
43 views

Recovered database files can't be attached

I have recovered my hard drive with my databases mdf and ldf files. When I want to attach them I get a 9004 error: Error: 9004, Severity: 21, State: 1. An error occurred while processing the log ...
3
votes
3answers
201 views

Update Table from SP in SSIS

I know there are similar questions to this one, but I could not find one that answered my question. Inside my current SSIS package... I have a dataflow task which... Takes output from a stored ...
2
votes
1answer
117 views

How can I combine two queries

I would like to combine these queries: UPDATE T1 SET Opening_date = T2.Opening_date FROM FactTransaction T1 INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey and UPDATE FactTransaction SET ...
1
vote
2answers
64 views

SQL Server 2008 having issues with HAVING clause

I have a question regarding a report I am currently writing. I need this query to only show me line items that have < -5 as mins_elapse Here is the code. I have left out the FROM clause to ...
2
votes
3answers
51 views

SQL Server: How to add a full join to two tables but have matches line up?

I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences ...
11
votes
2answers
282 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
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 +' ...
0
votes
0answers
18 views

How to identify a process that has been cancelled?

How can you identify a process that has been cancelled? for example: I was running this dynamic sql - via a linked server: declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf ...
1
vote
1answer
65 views

dynamically creating query in a loop [closed]

I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows WHILE (Data is in the ...
0
votes
2answers
121 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
8
votes
3answers
1k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
-2
votes
1answer
38 views

Last date SQL Server query required

Scenario: I am writing a cash management system and want to put some date columns for getting date info / history. Actually there are two columns for date in same table at this moment. Voucher Date ...
1
vote
1answer
62 views

SQL Server lock_escalation flag on ALTER TABLE — can I really restructure without a table lock?

SQL Server 2014 Standard Edition In my situation, I have a huge table (100 million+ rows), and need to: add column to it set default value in that column I see in ...
11
votes
2answers
319 views

Unable to mirror a database SQL Server 2012

When trying to mirror a database using the following command ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ I get the following error Msg 1475, Level 16, State 105, Line 1 ...
5
votes
1answer
146 views

Why does this query not use the index that exists on SURNAME?

There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used. DECLARE @SURNAME VARCHAR(30) = 'test' SELECT SURNAME FROM PATIENTS AS PAT WHERE ...
9
votes
1answer
593 views

Are explicit transactions needed in this while loop?

SQL Server 2014: We have a very large (100million row) table, and we need to update a couple of fields on it. For log shipping, etc, we also, obviously, want to keep it to bite-size transactions. ...
3
votes
1answer
52 views

Query to list “last backup time” listing wrong times ( SQL Server 2008 R2 )

I use this query to list last backup dates of our databases: SELECT SDB.NAME AS DATABASENAME, COALESCE(CONVERT(VARCHAR(12), MAX(BUS.BACKUP_FINISH_DATE), 101),'-') AS LASTBACKUPTIME FROM ...
7
votes
3answers
269 views

sql server: updating fields on huge table in small chunks: how to get progress/status?

We have a very large (100million row) table, and we need to update a couple of fields on it. For log shipping, etc, we also, obviously, want to keep it to bite-size transactions. Will the below do ...
1
vote
0answers
27 views

How to parse and display row data as Column

I'm trying to display the row data from tbl_cte as a column. The result of column1 is alphanumeric e.g : "Data1.2.3". I need to parse it and display the "Data" as the name and "1.2.3" as the version ...
1
vote
1answer
72 views

How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL [duplicate]

I have many procedures that I use the option WITH ENCRYPTION when I create them. You can see this on the picture below, as an example: the problem with this is that when I want to save my previous ...
1
vote
1answer
338 views

How to add/update a column with an incremented value and reset said value based on another column in SQL

I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them. I tried rewriting the search term over and over but I guess I do not ...
8
votes
1answer
97 views

How can I get the hierarchical values from the below query?

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID. I need to get all the category IDs and their subcategory ...
2
votes
1answer
61 views

Stored procedure suddenly very slow, updating statistics not helping

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have ...
3
votes
1answer
71 views

Find the most recent row where no exact match on date is found

I am trying to write a query that tries to extract the value of the exchange rate where: Query date = the exchange date; and Query currency = the specified currency If it does not contain a ...
6
votes
1answer
68 views

Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

SET STATISTICS XML ON; DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')); Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect ...
8
votes
3answers
207 views

What is lost when I create a Foreign Key using `WITH NOCHECK`?

I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate. And if it is not trusted (like when I create the FK using WITH NOCHECK) ...
40
votes
5answers
1k views

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
4
votes
2answers
784 views

Listing SQL Server instance startup parameters

I have a server I have just restarted and verified which trace flag are active using DBCC TRACESTATUS: Trace Flag : 3688 Function: Removes messages to errorlog about traces started and stopped ...
2
votes
1answer
99 views

How to map a drive in a SQL Server script

I am trying to execute below script to Network a Drive use master go exec sp_configure 'show advanced options', 1; go reconfigure go exec sp_configure 'xp_cmdshell',1; go reconfigure go EXEC ...
5
votes
3answers
138 views

Charindex, Getting specific data from a string

Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error. I searched for similar problems on stackexchange as well as a ...
4
votes
0answers
93 views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ...
-1
votes
3answers
82 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...