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
1k 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
112 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
128 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
200 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
358 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
81 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
191 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
110 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
540 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
111 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
130 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 ...
1
vote
1answer
793 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
50 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
207 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
193 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
439 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
0answers
187 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
614 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
346 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
472 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 ...
0
votes
1answer
448 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 ...
6
votes
2answers
2k 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 ...
3
votes
3answers
884 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
322 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
597 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
119 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
47 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 ...
5
votes
2answers
445 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
160 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
253 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
297 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
293 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
91 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
143 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
730 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
752 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
262 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
62 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 ...
14
votes
6answers
1k 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
215 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
554 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
120 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
329 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 ...
4
votes
2answers
190 views

Fulltext search limiting users possibilities

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure. CREATE PROCEDURE [dbo].[SearchPages] ...
3
votes
3answers
747 views

Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
2
votes
1answer
345 views

Create a sub query for sum data as a new column in SQL Server

Suppose that I have a table named tblTemp which has this data: | ID | AMOUNT | ---------------- | 1 | 10 | | 1-1 | 20 | | 1-2 | 30 | | 1-3 | 40 | | 2 | 50 | | 3 | 60 | ...
2
votes
1answer
52 views

Strategies for summary data in parent record based on detail data?

I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
1
vote
1answer
71 views

Sql Server Server Group query

Scenario I have 4 front-end sql server. All of them have the same db instances. Data are replicated from back-end databases. Issue In SQL Management Studio I have registered the servers and I have ...
2
votes
1answer
107 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
0
votes
1answer
188 views

How to add custom sequence number in table which is going to be populated automatically

I have table something like this CREATE TABLE [dbo].[ado_test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [tip] [varchar](10) NOT NULL, [datum] [datetime] NOT NULL, [doc_number] [int] ...