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

learn more… | top users | synonyms (1)

2
votes
1answer
351 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 ...
0
votes
1answer
122 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
3
votes
1answer
161 views

Including null values in unpivoted column

Consider the following table: id title genre1 genre2 genre3 genre4 genre5 1 Movie1 Thriller Crime NULL NULL NULL 2 Movie2 Fantasy NULL NULL NULL NULL 3 Movie3 ...
-4
votes
1answer
80 views

How to Pass table type as Parameter in T-Sql like Postgresql [closed]

In my company like to move from PostgreSQL to SQL Server. Doing this, we need to migrate pl/Pgsql functions to T-Sql. In pgsql function we are create and pass type as parameter. Same option had in ...
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
61 views

Defining a column as nullable in a view (but not in the referenced table)

My company is working on implementing a new HR application that will unify our existing HR processes. We've run into a bit of a sticking point, though; according to business rules, the EmployeeID and ...
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
259 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
16
votes
1answer
8k views

What exactly does “No Join Predicate” mean in SQL Server?

MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate". But unfortunately it does not seem to be as easy as that. For example, very ...
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 ...
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 ...
0
votes
1answer
2k views

LEFT vs CHARINDEX for matching the start of a string in T-SQL

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing ...
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
113 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 ...
1
vote
1answer
119 views

How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database. This is because I'm going to ...
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 ...
4
votes
1answer
604 views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. ...
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 ...
11
votes
2answers
582 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, ...
0
votes
1answer
72 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
286 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 ...
3
votes
1answer
298 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
1
vote
1answer
214 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: ...
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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
185 views

Prevent record from being inserted in the table in After Trigger

I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB. I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId ...
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 ...
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 ...
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 ...
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 ...
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
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 ...
9
votes
3answers
6k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
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 ...
9
votes
1answer
591 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. ...
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 ...
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, ...
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 ...
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 ...
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
0answers
212 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
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
334 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 ...