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
353 views

How can I make this query sargable?

I have a query which joins two tables based on a timestamp value (don't ask). One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar. ...
4
votes
3answers
454 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) + '.' ...
0
votes
0answers
27 views

Question? Creating a website that has 2 sets of intermidate tables

I am creating a web site that uses 6 tables from one database in mysql (example) 2 sets of tables that connect to intermediate tables (using foreign keys to join the tables). 1) Category -> ...
13
votes
2answers
355 views

Paging performance with customizable sorting over many millions of rows

In our application we have a grid where users can page over a large number of records (10-20 million). The grid supports sorting in ascending and descending order in a number of columns (20+). Many of ...
36
votes
6answers
2k views

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 ...
3
votes
2answers
155 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
2answers
214 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
64 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; ...
0
votes
1answer
179 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
23 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
423 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 ...
1
vote
2answers
391 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 ...
1
vote
2answers
79 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 ...
6
votes
5answers
8k views

Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Why does something like this not work? SELECT CASE WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL ELSE Somecol END AS MyTest FROM Customers; I am just checking if the column ...
0
votes
2answers
64 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
4
votes
1answer
255 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
2k 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 ...
1
vote
0answers
95 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 ...
6
votes
1answer
157 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 ...
5
votes
1answer
252 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 ...
1
vote
2answers
150 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 ...
11
votes
1answer
833 views

How can I make a query to take a specific amount of time?

I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have ...
4
votes
6answers
3k 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 ...
0
votes
4answers
54 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 ...
4
votes
1answer
13k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
2
votes
1answer
867 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 ...
0
votes
1answer
63 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
86 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 ...
1
vote
2answers
318 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
63 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
44 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
0answers
191 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
102 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, ...
1
vote
1answer
194 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 .. ...
0
votes
1answer
104 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
0answers
43 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
34 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 : ...
0
votes
1answer
55 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
0answers
156 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 ...
4
votes
2answers
261 views

Fulltext search limiting users possibilities

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure. CREATE PROCEDURE [dbo].[SearchPages] ...
1
vote
1answer
119 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
52 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
252 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
89 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
123 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 ...
7
votes
2answers
684 views

Does MERGE use tempdb?

Consider the following query: MERGE [Parameter] with (rowlock) AS target USING (SELECT @AreaId, @ParameterTypeId, @Value) AS source (AreaId, ParameterTypeId, Value) ON ...
0
votes
1answer
1k views

Stored procedure to compare two columns from different tables and make the insert

I have two tables: Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer) Timesheet with columns fld_id (integer), fld_allocated_time (integer), ...
9
votes
1answer
874 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 ...
1
vote
3answers
720 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
0
votes
2answers
145 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 ...