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

learn more… | top users | synonyms

5
votes
1answer
324 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
146 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
535 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
457 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
2k 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
91 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
304 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
494 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
1k 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
1k 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
40 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
550 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
681 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
171 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
2k 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
342 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
586 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
705 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 ...
11
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
870 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
433 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
259 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
115 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
156 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
77 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
616 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
2k 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
71 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
108 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
108 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
1answer
547 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 ...
1
vote
3answers
4k 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 ...
6
votes
4answers
675 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
176 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 ...
1
vote
1answer
197 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
3
votes
1answer
317 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
68 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
327 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
57 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
192 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
182 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 ...
2
votes
3answers
3k 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 ...
4
votes
2answers
208 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 ...
1
vote
1answer
323 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
5
votes
2answers
481 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
1answer
568 views

How to split the comma separated list in the stored procedure

I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
6
votes
1answer
1k views

Restore a Database to a New database (T-SQL)

I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
13
votes
1answer
5k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...
4
votes
1answer
109 views

Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

Expressions like NOT IN (1,2,3) are valid in a where clause: SELECT foo FROM bar WHERE ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22)) However if I want to move an elaborate expression ...