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
283 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
297 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
200 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
173 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
776 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 ...
3
votes
3answers
891 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
86 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
131 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
780 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
258 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
867 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
521 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
452 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
104 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
542 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
637 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
395 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
137 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
113 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
90 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
6k 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
95 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 ...
5
votes
1answer
237 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
0
votes
1answer
130 views

Oracle equiv of T-SQL UNION ALL query

In T-SQL I can accumulate the results of two unioned queries using the following syntax: SELECT Q.a, SUM(Q.b) FROM ( SELECT f1 as a, f2 as b FROM TBL1 UNION ALL SELECT f1 as a, f2 as b FROM TBL2 ) ...
-7
votes
2answers
468 views

How can I check for SQL Server performance problems using T-SQL? [closed]

I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems ...
3
votes
3answers
310 views

Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included. Is ...
1
vote
1answer
900 views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
0
votes
0answers
84 views

trap xp_logininfo stored procedure error

Sql server 2000, windows 2003 server. I am trying find all the invalid domain login creates in sql 2000 server in a script using xp_logininfo and get message "Could not obtain information about ...
4
votes
2answers
251 views

Update strings for all cmdexec Jobs

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server? We'd like to change our logging location, and in principle I'd think this could be ...
2
votes
1answer
349 views

Execute sp_WhoIsActive as a query

I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?
1
vote
1answer
569 views

how to add attachment(text file) to database mail?

I have scenario Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates I can send a mail to client that job is ...
9
votes
3answers
883 views

Unique Identifier with Extra Characters Still Matching in Select

We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID. For ...
0
votes
1answer
38 views

Copy Permission From Windows Based user To Local user

I want to know if it is possible to copy the permissions of a windows user to a locally created user on SQL server?
0
votes
2answers
278 views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
2
votes
2answers
408 views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
2
votes
2answers
165 views

Help with tricky update statement

I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier. I have two table variables: DECLARE ...
3
votes
2answers
1k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
0
votes
2answers
309 views

Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment. I have a web based form with multiple ...
6
votes
2answers
475 views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
4
votes
2answers
484 views

Subquery in the VALUES clause

Here's a real-world schema: CREATE TABLE [dbo].[Setting] ( [ID] [BIGINT] NOT NULL, [Module] [NVARCHAR](400) NOT NULL, [Property] [NVARCHAR](400) NOT NULL, [Value] ...
3
votes
3answers
2k 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 ...
10
votes
1answer
2k 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
votes
1answer
596 views

How to set the timeoffset in a Datetimeoffset column without changing the date part

Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well? In the post below, we can see the SWITCHOFFSET will ...
3
votes
1answer
343 views

LIKE character length limitation

The background to this question can be found here. The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
4
votes
1answer
217 views

Is it possible in SQL Server to change default “order by” direction from asc to desc?

This question came up in a discussion at work... given the following T-SQL statement: select firstname, lastname from users order by lastname; the default sort order on the order by clause is asc. ...
0
votes
1answer
108 views

Changing data type from DATE to CHAR(10) with a query

I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query. I ...
1
vote
1answer
122 views

Parameter triggers different execution plan, but why?

I have a table CREATE TABLE [dbo].[Numbers] ( [Date] [date] NULL, [Time] [time](3) NULL, [Value] [char](10) NULL ) and the table has > 10 Billion rows, therefore it ...
2
votes
1answer
76 views

Synchronize Queries Retrieving Sessions And Locks From DMVs

I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
3
votes
2answers
519 views

Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions. We need ...