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

learn more… | top users | synonyms

-1
votes
1answer
1k 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
179 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
144 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
111 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 = ...
1
vote
5answers
25k 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
527 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
569 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
181 views

Oracle equiv of T-SQL UNION ALL query [closed]

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 ) ...
-8
votes
2answers
891 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 ...
1
vote
1answer
4k 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 ...
5
votes
2answers
937 views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , ...
0
votes
0answers
125 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
546 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 ...
11
votes
3answers
6k 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
52 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?
1
vote
2answers
4k 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
187 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 ...
2
votes
2answers
1k 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 ...
3
votes
1answer
3k views

How to remove xmlns from child elements with FOR XML

I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely: - http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document - A custom one that ...
3
votes
2answers
4k 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
706 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 ...
2
votes
1answer
643 views

Rules in SQL Server 2005

The syntax for rules in SQL Server 2005 is given: CREATE RULE [ schema_name . ] rule_name AS condition_expression Whenever using rule, for example: CREATE RULE list_rule AS @list IN ('1389', ...
7
votes
2answers
1k 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
2k 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] ...
12
votes
1answer
6k 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 ...
8
votes
1answer
345 views

Variable for element name

I have this tSQL code which works OK: SELECT c.logguid, a.b.value('./PropertyValue', 'varchar(max)') asd FROM [dnn].[dbo].[EventLog2] c cross apply ...
3
votes
1answer
1k 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
425 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
155 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 ...
2
votes
1answer
424 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
90 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" ...
4
votes
2answers
885 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 ...
0
votes
1answer
5k views

how to return table on sql function

I am very new to sql create function i wanted to check data column value at table the purpose is to return full table where data is null , <0 , >1 the problem is i am trying make data is ...
1
vote
1answer
72 views

Reference the database from a stored proc?

I'm using this T-SQL to get index stats (edited for brevity): SET @db_id = DB_ID(N'TestDB'); SET @object_id = OBJECT_ID(N'TestDB.dbo.Users'); SELECT * FROM sys.dm_db_index_physical_stats(@db_id, ...
7
votes
1answer
120 views

Trouble with SQL and aggregates

With this query: SELECT SUM(qty) AS sumQty, cnetprodid FROM quoteitem WHERE ordered = 1 AND sageSOPOrderReturnLineID IS NOT NULL AND LEN(LTRIM(RTRIM(cnetprodid))) > 0 ...
3
votes
1answer
115 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
2
votes
3answers
17k views

Non-Clustered-Index on a temporary table

I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is ...
2
votes
1answer
1k views

return percentage of records based on value in column

I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below ...
7
votes
4answers
1k views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
1
vote
2answers
539 views

How to link each column in sql?

I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
3
votes
1answer
345 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
4
votes
1answer
76 views

SP for Restoring Database and performing other tasks on it

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
0
votes
0answers
599 views

RAND in T-sql function

Can any one tell why t-sql not allowing using RAND function in side a function SELECT CAST (RAND() * 10000000 as varchar(20)) the error is Invalid use of a side-effecting operator 'rand' ...
1
vote
1answer
59 views

Does implicit casting negatively affect index usage?

I have a query similar to the following: SELECT id FROM MyTable WHERE eventCount > eventCutoff * eventFactor eventCount and eventCutoff are defined as a smallint, and eventFactor as a float. I ...
0
votes
1answer
322 views

Having trouble with a T-SQL scalar variable during a create table [closed]

I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table. Can anyone tell me what is wrong with this script? -- T-SQL MSSQL SET NOCOUNT ON DECLARE @tt = ...
3
votes
2answers
320 views

Find top by values by percents using range in SQL Server

What will be easier way to group records by range of percents. Here what I try to accomplish Select top percents between 50 and 75 * from myTable order by PKI Here is way I did this but I have ...
4
votes
1answer
4k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
4
votes
2answers
220 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
3
votes
3answers
6k views

Inserting rows into other table whilst preserving IDENTITY

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
16
votes
2answers
3k views

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table ...