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

learn more… | top users | synonyms

2
votes
1answer
273 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
568 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
117 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
44 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
418 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
157 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
245 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
247 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
285 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
141 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
626 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
706 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
257 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
59 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
973 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
192 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
474 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
114 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
299 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
184 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
684 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
295 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
67 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
175 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] ...
1
vote
1answer
395 views

Autogrow of database1_log file in database database1 took 1010871 milliseconds

in past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes. Now i am running a simple query: UPDATE query query has ...
-1
votes
1answer
69 views

validate where each parent intermediary is also a parent to itself [duplicate]

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any. I need to do is first of all get all parents ...
1
vote
2answers
355 views

How can I run a T-SQL Script using SQL Credentials?

I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the ...
0
votes
0answers
260 views

How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]

The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
4
votes
1answer
186 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting ...
1
vote
0answers
1k views

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'

I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
4
votes
3answers
1k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
1
vote
1answer
100 views

Can I use Try_Cast to give me a query result column with different datatypes?

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need). I am trying to make a view for this table that allows the value to display ...
1
vote
3answers
160 views

DB comparer field by field for all tables

I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added ...
1
vote
1answer
1k views

How can we get table name from inside trigger?

I need to pass the table name from trigger to procedure. ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects] AFTER INSERT AS BEGIN SET NOCOUNT ON; -- procedure call ...
2
votes
2answers
337 views

limit the number of rows returned when a condition is met?

Is it possible to limit the number of rows returned when a condition is met? I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per ...
0
votes
3answers
1k views

Easier way to handle so many isnull() situation

Is there any good way to avoid writing so many times isnull() function inside sproc ? I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
2
votes
2answers
682 views

Executing a table type parameter stored procedure or function within a select clause

Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
3
votes
1answer
800 views

update statement with self join

I needed to copy information from one row of a table to another. I noticed I could do the following: update address set col1 = a2.col1, col2 = a2.col2, .....etc from address a1, address a2 where ...
0
votes
1answer
164 views

Conditional Where Statement Involving a NULL value?

Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how ...
3
votes
3answers
875 views

Conditional WHERE Clause in a Table Valued Function

I have a table valued function CREATE FUNCTION [dbo].[DateRange] ( @StartDate date, @EndDate date, @Location varchar(25), @Device varchar(25) ) ...
7
votes
2answers
1k views

How to flatten results of a table with two related “many” tables?

I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them. I have the following tables (somewhat abbreviated ...
-1
votes
1answer
494 views

how cursor implementations are different for each cursor type in sql server

In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
1
vote
3answers
152 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
2
votes
1answer
118 views

how to show only changed rows

if want querying the following table : EventDateTime (DateTime), Host (guid), Flag ( bit) 2013-04-01 05:00, {id-x}, 0 2013-04-01 05:01, {id-x}, 0 2013-04-01 05:02, {id-x}, 0 2013-04-01 05:03, {id-x}, ...
0
votes
1answer
105 views

Why is there such a huge performance difference in these two join statements?

I have a query which i first wrote as (query has been simplified a bit) : select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c inner join product p on p.indID = ...
0
votes
5answers
8k views

The DELETE statement conflicted with the REFERENCE constraint

I'm trying to delete all users but getting the error: Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict ...
0
votes
1answer
126 views

Need help in converting this cursor to a set based operation

I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest ...