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
1answer
257 views

How to insert and or update a variable number of records and prevent concurrency issues

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be ...
0
votes
1answer
286 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
1
vote
1answer
1k views

Getting collation confict in IF statment and need help resolving it please

A SQL script I am writing is generating the error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation. I ...
1
vote
1answer
2k views

How to find column with all same repeating character

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why ...
-3
votes
1answer
95 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
2
votes
1answer
135 views

Query does not use index when selecting all records [closed]

This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan. I have setup an index on a specific table: CREATE ...
0
votes
1answer
5k views

Uncommitable T-SQL transaction after trigger applied to table

Good morning, I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record ...
1
vote
2answers
143 views

Improving performance of query with `WHERE <> ''`

I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case] Is there any way to improve the performance of the query SELECT * FROM myTable WHERE ...
0
votes
1answer
168 views

Can I design this database better? [closed]

I have the following database design: Script to reproduce: /* DROP TABLE [dbo].[feedback] DROP TABLE [dbo].[action] DROP TABLE [dbo].[download] DROP TABLE [dbo].[user] DROP ...
1
vote
1answer
373 views

Simple update query balloons tempdb

I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is ...
1
vote
1answer
691 views

Check for column in backupset table

Created a script to show last backup locations, sizes and dates SELECT DatabaseName = x.database_name, LastBackupDate = x.backup_finish_date, BackupSizeKB = x.backup_size, ...
0
votes
1answer
139 views

How do I setup a foreign key relationship based on two columns?

I have the following two tables: CREATE TABLE [dbo].[company] ( [CompanyID] [int] NOT NULL , [CompanyName] [varchar](150) NULL , [DepartmentID] [int] NOT NULL , ...
0
votes
1answer
271 views

T-SQL Gurus, I need help optimising these queries for performance

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids In a production environment the table ...
1
vote
0answers
200 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
2
votes
1answer
1k views

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) ...
1
vote
2answers
156 views

Optimize Query of Count Less Than

I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, ...
1
vote
1answer
180 views

How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it. The SP filters out all the calculated columns and other stuff that stops you doing ...
2
votes
1answer
2k views

What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n) So I wrote the following queries to just check (as I'm learning T-SQL) ...
3
votes
1answer
58 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...
1
vote
2answers
529 views

Where can I find scenarios to work through T-SQL [closed]

I've been working through the exam guides for the new 70-461 MCSE data exams and i'm at a point where I want to practice my T-SQL. Does anyone know of any good resources whereby scenarios are posed ...
0
votes
3answers
623 views

Dividing in SQL

can anyone explain to me why this query returns 0.000? SELECT CAST(20/1024 AS NUMERIC(10,3)) I am just trying to do a simple conversion of MB to GB Thanks
0
votes
1answer
912 views

Extract time portion of Datetime2(7) in hhmmssfff format

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this: DECLARE @mdate AS DATETIME2(7) SET @mdate = '2012-03-15 10:13:27.5437431' SELECT ...
3
votes
1answer
243 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
0
votes
1answer
1k 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
votes
1answer
757 views

Totals Column - Blank if Zero or NULL

A report currently computes a "totals" column like so: ,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) + ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0 THEN '' ...
3
votes
1answer
914 views

Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

For a TSQL report, I have a one-to-many parent-child relationship. I want to report both parent and child relationships in the same report, like so, with the parent columns on the left and child ...
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 ...
9
votes
2answers
5k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
4
votes
3answers
3k views

Select rows after a specific ID

I have the following query, it chooses from a news articles table. Due to multiple sorting factors, I cannot locate the next page of date. The parameter I have is the last ID in the query result set, ...
2
votes
1answer
798 views

How to update a SET statement so I can use the MIN() function?

I have an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use ...
1
vote
3answers
1k views

How can I drop a stored procedure whose name is in a variable?

Given a variable that contains a stored procedure name: DECLARE @stored_procedure_name sysname; SET @stored_procedure_name = 'some_stored_procedure_name'; How can I drop the stored procedure?
1
vote
0answers
163 views

Optimising Large SQL Server Tables [closed]

I have a large table with around 50 million rows and 15 columns. Whenever I read, I always need all columns so I can't split them. I have a clustered index on the table with 4 keys (ALL INT) and I ...
1
vote
1answer
56 views

Foreign Key off of a UNIONed View

I have a view that looks like this: CREATE view reference.Test WITH SCHEMABINDING as SELECT reference.HighlevelTestId as TestId, Name, IsActive, cast(1 as bit) as ...
7
votes
2answers
710 views

Does MERGE use tempdb?

Consider the following query: MERGE [Parameter] with (rowlock) AS target USING (SELECT @AreaId, @ParameterTypeId, @Value) AS source (AreaId, ParameterTypeId, Value) ON ...
0
votes
1answer
281 views

UPDATE statment with and without SELECT keyword

I have been doing some testing on removing the SELECT keyword from this update: Update #tmpDriverTime Set WorkDay2 = (Select IsNull(Sum(PyblHrs),0) From spr_DriverTimeEntry Where ...
4
votes
1answer
359 views

Tips on improving/refactoring slow query?

Let's play lotto! Ok. I have a numbers table with 80 rows (numbers 1-80): create table dbo.numbers (knum tinyint primary key identity); I have a table with the drawid, and each number drawn. ...
2
votes
2answers
784 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
0
votes
1answer
356 views

RTF to Plain-Text? [closed]

Did you ever get the library from this post? We have a need to convert whole, large, whole tables worth of RTF information into something usable and would rather not reinvent if possible. Converting ...
0
votes
2answers
103 views

Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Scenario: Lets say I have several tables for a software ticketing system: Developer, Supervisor, TicketType, TicketStatus and a Ticket table to join them on. Assume I have done my inner joins and now ...
1
vote
1answer
263 views

Is there a way to extract a report of or script all of the primary keys in a database?

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of ...
7
votes
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
3
votes
1answer
1k views

Understanding SIX lock in Microsoft SQL-Server

Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of ...
-2
votes
1answer
621 views

How to enter multiple checkbox list items into database for eventual reporting [closed]

(I tried posting this on SO but went unansered. I'll just post these theory type questions here from now on) I'm in the process of creating the first of a few CheckBoxList for a manually created ...
0
votes
1answer
65 views

Give access to view or table?

I want to give an external user (new login) read access to part of the data model from my Database. The user will copy the data model and content from my Database, and then integrate that into their ...
16
votes
6answers
3k views

Trying to find the last time that a value has changed

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table. Records are inserted into this table periodically. The ID will always stay the same but ...
0
votes
1answer
532 views

Simplify subqueries with join

I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data. ...
0
votes
1answer
1k views

EXPORT specific columns to INSERT statements

Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements? I am trying to generate these for unit testing based on a large production table so using ...
4
votes
2answers
143 views

Difference Between SP with BEGIN/END and without BEGIN/END

I have found some SP(stored procedure) written as, CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) BEGIN --- Actual Work END and some as CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) --- ...
0
votes
1answer
707 views

Searching for keywords in fulltext indexes using the “contains” function

Lets assume "and" is not a stopword, in my language it should not be. How do I search for it using the contains function? It is a keyword, but how do I escape the keywordness? No luck with: SELECT ...