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
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 ...
0
votes
1answer
998 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
67 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
106 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
359 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 ...
0
votes
3answers
2k 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
511 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
153 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
158 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
310 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
66 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
273 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
162 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
162 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
2k 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
206 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
251 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
426 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
530 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
867 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 ...
11
votes
1answer
3k 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
98 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 ...
9
votes
1answer
519 views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
2
votes
1answer
1k views

exist (select * from my table)

My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks. Query (1): IF ( SELECT COUNT(UnitTrustCounterId) FROM ...
1
vote
1answer
727 views

SQL Server Full-Text Indexer with stoplists/stopwords

As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me. This ...
2
votes
1answer
522 views

error in Execute SQL Task with parameter

I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
-3
votes
3answers
1k views

Join tables on a partial string match

input: I have a table containing (any number of) user-supplied strings: ╔═══════╗ ║ Value ║ ╠═══════╣ ║ To ║ ║ An ║ ╚═══════╝ And a table of user details: ╔════════╦══════════╗ ║ UserID ║ ...
1
vote
2answers
475 views

Bitwise operations in TSQL

Take the following code: Declare @a integer set @a = 0 set @a = @a | cast (1 as BINARy) set @a = @a | cast (100 as BINARy) select cast (@a as integer) Now, shouldn't the output of this code be 5 ? ...
4
votes
3answers
415 views

Dynamic database reference in T-SQL scripts

We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively). Inside foo family of databases there's a view that should ...
4
votes
1answer
185 views

T-SQL and GPS co-ordinates

I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...
2
votes
2answers
195 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
1
vote
2answers
2k views

Create job to send alert using sp_send_dbmail when table value greater than x

PROBLEM The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached. TABLE CREATE CREATE ...
0
votes
1answer
72 views

modify T-SQL to get results

I am an amateur. I am trying the following select * from [dbo].[AccessFeeDetail] where LastLoginDate >= '10/15/2012 00:00:00.000', jobname not like 'Uptest_%' I am making a mistake on where ...
1
vote
1answer
260 views

Stuff function in T-SQL

select STUFF( STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] from sysjobhistory The query I have ...
1
vote
2answers
2k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
1
vote
0answers
102 views

TSQL Trigger Incorrect Syntax

Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G: INSERT INTO aTable ( tTypeId, opId, inserted.Id ) SELECT 3, 0, inserted.Id ...
6
votes
2answers
521 views

T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
0
votes
3answers
238 views

Pivoting row contents to produce one dimensional result

I have a table with a varchar column containing pipe-delimited values For example: Row 1 Column 1 = a|b|e|gg|foo Row 2 Column 1 = oV|foo|do Row 3 Column 1 = boop How can I query this to return ...
5
votes
4answers
672 views

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
2
votes
2answers
841 views

Merge two records in the same table, keeping foreign key relationships intact for both

I have a table, called SITES, that has three columns, lets say it looks like this: ID Name Path 1 Google http://www.google.com/ 2 Microsoft http://www.microsoft.com/ I also have ...
2
votes
1answer
218 views

How are foreign keys resolved when creating tables in batch?

I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
4
votes
1answer
438 views

Calculating Median Value within a Group By

I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is ...
2
votes
2answers
1k views

How to map another server through SQL Server Management Studio

I am trying to map another server by giving the command EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes' I got an error with this network path not ...
0
votes
1answer
339 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
3
votes
2answers
1k views

Linked Server Query Results: OPENQUERY vs Distributed Transactions

I have two databases. They are on different servers. One is named REPORTDB and the other is named TRANSDB. TRANSDB is referenced as a linked server on REPORTDB named TRANS. I execute a query like ...
4
votes
1answer
1k views

How can I create a stored procedure using xp_fixeddrives?

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008 ALTER PROCEDURE freespace AS ...
1
vote
1answer
278 views

Query tuning help needed (Hash Match and Table Scans)

I need help optimizing the following query (returning ~8k rows): SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name) FROM ( ...