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
2answers
68 views

Rebuilding indexes on a list of tables

I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the ...
-5
votes
0answers
46 views

Incorrect syntax near the keyword 'AS' [on hold]

string strQry = @"declare @state varchar(max) declare @query varchar(max) set @state=(Select State from CandidateInfo where Id=1501000040) set ...
5
votes
2answers
130 views

T-SQL brain teaser: find non-overlapping sets

I would like to ask for help coming up with a query, which can identify groups on non-overlapping records. Here is a sample scenario (admittedly contrived). Let's say I have employees who are ...
1
vote
1answer
23 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
-2
votes
0answers
42 views

Please help me in resolving the error: The definition of object 'trigger name' has changed since it was compiled [on hold]

I'm trying to upgrade SQL Server 2000 to 2008 R2. After the upgrade I'm getting the below error when trying to execute the stored procedure.This is executing in SQL Server 2000 without any issues, but ...
4
votes
3answers
445 views

Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other ...
0
votes
1answer
38 views

Breaking a big insert query into smaller ones

I have the below INSERT query. It tries to insert over 50,000 rows and it fails because of logging. Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT ...
0
votes
0answers
17 views

Cursor brings back Null when run

I'm trying to create a proc which assists in creating an XMLSitepath. I am trying to use a cursor but its not behaving as it should. Please could someone take a look and tell me where I am going ...
2
votes
1answer
46 views

Results for every hour of the day even data if not present

I am trying to write a query to show the count of users for each hour of the day. If there is no data for that hour, I want to record the hour with a count of zero. Source data ...
4
votes
3answers
74 views

Help with SQL Query to XML output

I have the following query: SELECT [ID] AS [NewID] , ( CASE WHEN LEN([MiddleName]) > 0 THEN [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.' ...
1
vote
2answers
35 views

Filling in date holes in grouped-by-date SQL data

The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue: -- create example table DROP TABLE dbo.EventRecords GO CREATE TABLE dbo.EventRecords ...
28
votes
6answers
789 views
+500

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
1
vote
1answer
56 views

Is someone trying to hack into my SQL Server Database?

I was asked by a friend, who isn't technical at all, to help her with her application. I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot ...
3
votes
1answer
99 views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. ...
3
votes
2answers
158 views

SET DATEFORMAT problems

I have some doubts about SET DATEFORMAT command. 1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my ...
-3
votes
0answers
33 views

Open tran for Select query [duplicate]

I have seen BEGIN TRAN on queries in my production db, but it is just a SELECT query with NOLOCK option in the transaction. Why would a simple SELECT query be placed inside of a transaction?
1
vote
0answers
94 views

Most Efficent way WHERE [Date] = Today [duplicate]

Was wondering what would be the most efficient way to get data from today, does converting the date something like WHERE CONVERT(CHAR(8),[DateTimeColumn],112) = CONVERT(CHAR(8),GETDATE(),112) Not ...
0
votes
2answers
150 views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
1
vote
1answer
47 views

Stored procedure - problem with merge - Insert

I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct? Any help is appreciated. Here is ...
1
vote
2answers
62 views

How do I return data from a table where the data is the max date of the previous month's data submissions (-1 of current date)?

I have a stored procedure where I select the latest/max submit_date data for a tracker_date and client from a source table called Tracker_Live, and insert it into a new table called ...
5
votes
1answer
233 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
6
votes
1answer
136 views

What is the difference between <> All and Not In

Could you please tell me the differences between select CustomerId from Customer Where TerritoryId <> All(select TerritoryId from Salesperson) select CustomerId from Customer where TerritoryId ...
1
vote
2answers
74 views

Reuse a complex non-deterministic expression in SELECT and WHERE clauses

I have a query like this: -- This is inefficient, and may even be wrong if expression is nondeterministic SELECT TOP 10 team_name,score_points=<big expression> FROM teams WHERE <big ...
1
vote
1answer
107 views

T-SQL View — How to 'pre-fetch' schema using scalar function, then populate using table query

I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to ...
0
votes
4answers
41 views

Setting Select Permission on 100+ tables in a database

I got the 'Select permission denied' error for some of my tables in a database. In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties ...
2
votes
1answer
66 views

Why choose a top query and temporary table instead of a cursor for a loop?

When it comes to looping over a large set of data in T-SQL, the majority of examples I see on the Internet use a pattern like this: declare @someVariable int declare @remainingRows int select ...
4
votes
6answers
147 views

Select rows with same id but null and some other value in another column for that id

I want to get only rows having a value NULL and some other value than NULL for a particular username column. If both rows have null for that particular username or both have some values other than ...
1
vote
2answers
72 views

Writing a large update/delete script that doesn't provoke locks

While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for ...
1
vote
0answers
58 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
0
votes
1answer
22 views

convert update field second to time

I have 2 fields ( about 1000 records) name FROMTIME and TOTIME. Both fields will have time in seconds (for example FROMTIME = 20520 and TOTIME = 39360). I would like to convert and update these ...
0
votes
1answer
35 views

Dynamic Pivot with overlapping years

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the ...
1
vote
1answer
63 views

How to get the dependent entities (referencing entities) for an User Defined Function (UDF)

We have a database that has been copied, including stored procedures and some user defined functions. I'd like to check whether a UDF is being used. Is there a way to see which stored procedures ...
0
votes
0answers
25 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
1
vote
1answer
77 views

Rebuild index in table with many values

I'm junior DBA ,have table with 11 million values ,one of the indexes is like : CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] ( [my_id] DESC )WITH (PAD_INDEX = OFF, ...
0
votes
1answer
46 views

Delete duplicate records by column 1 but having differ values in column 2 and column 3 [closed]

I have a table like: SSN FName LName 1 a b 1 a b 1 b b -------------> to delete 2 c d 2 c d as above example I want to delete such records where SSN has different values in ...
1
vote
1answer
65 views

Create nonclustered index on uniqueidentifier type

CREATE TABLE [dbo].[session] ( [id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [session_id] [uniqueidentifier] NOT NULL, [supervisor_id] [int] NULL, some columns .. ...
1
vote
0answers
25 views

Export hierarchy tree in a specific way

I need a big help. I have two tables (SubCells and Codes) and Visually it looks like this (We can count an order) And i need to get this final result (with SQL or even a C#) How can i do ...
0
votes
0answers
30 views

Count rows in dataset SSRS2012

I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression : =CountRows("DataSet1") But I have the following error message: "The expression ...
0
votes
0answers
14 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
1
vote
0answers
36 views

Exporting to excel from OPENROWSET with cell formatting

I am trying to generate a report from SSMS to Excel, using OpenRowSet. All is working fine, still I have to export a specific column with a barcode font. I already have the column calculated from ...
2
votes
0answers
17 views

SELECTs in Database Change Log and Source Control

We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right, ...
0
votes
1answer
36 views

databases cross references baselines

We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. ...
1
vote
1answer
44 views

how to define trigger scope variable [closed]

My trigger is meant to calcalute the grade of a student base on the column value for every insert.But if a column value for the column is modified it the corresponding Grade should change. Currently i ...
0
votes
0answers
43 views

Backup table is larger than original table

I am cleaning up my database by deleting unwanted tables such as temp tables, backup tables. While doing clean up I see that I have a backup table and the original table. The size of the backup table ...
0
votes
1answer
70 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
0
votes
1answer
41 views

Import CSV file - dynamic path

I am trying to import a csv file dynamically. This works: BULK INSERT #CSV FROM 'C:\Users\MyName\Desktop\Players.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) I would like to ...
2
votes
0answers
57 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
2
votes
1answer
31 views

How can one select a static value as a column based when using UNPIVOT?

I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can ...
9
votes
1answer
702 views

TSQL Why is Top faster with a variable?

Morning all, I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications. I added in a select to ...
0
votes
2answers
43 views

SQL: TSQL to Find invalid views

we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities. The ...