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

learn more… | top users | synonyms

0
votes
0answers
27 views

Is it a good idea to use SSAS to predict hourly transactions?

So I've been working on this for quite some time but I run into so many obstacles that I am beginning to wonder if I am using the right tool for the job. My problem is pretty simple: I have several ...
2
votes
1answer
180 views

If “large value types out of row” table option is enabled, the table size is increased

I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table. A colleague of me told me if I enabled it, each field is stored ...
4
votes
1answer
203 views

How do you get an average of job history times?

The following is a script I found online that has most of the elements of what I desire in the result set. The only thing missing is how I can get an average of the run times over a decided time ...
0
votes
1answer
82 views

How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database. This is because I'm going to ...
0
votes
1answer
42 views

Find the remote userid causing updates on my table

Small question here. I found some answer close to it but not exactly what I'm looking for. Here is the situation: I have a SQL Server trigger on a table. Its task is to record the username (logon id) ...
-4
votes
1answer
96 views

Distinct count of 5 different fields based on multiple values?

I'm not sure how else to explain this so here is an example of what I am trying to accomplish. The problem with this, as you will quickly notice, is that there are repeats in the results. and there ...
1
vote
1answer
80 views

T-SQL Create Query - Compare and Calculate

I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points". Basically I got two tables. Source table: This is a table with statistical per ...
1
vote
1answer
96 views

Get all the data between Characters [closed]

I am trying to extract all the variable from the following string; string = 'factor1|factor2|factor3' This is just a test string. In real string, I do not know the number of factors. What I want is ...
3
votes
1answer
221 views

How do you run T-SQL commands on multiple servers from one place?

I have some daily and weekly tasks where I run a script to get the required information. What is the way to running this code once and hitting all the servers without having to log into each one of ...
1
vote
2answers
298 views

Assigning new auto increment value [duplicate]

I have a table that is created as below. CREATE TABLE [dbo].[Table1]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Title] [VARCHAR](255) NULL, ... Lets say that the latest ID is 100 ( I have 100 ...
0
votes
1answer
82 views

Validate a column to accept only certain age limit from birthdate given using check constraint [closed]

A business rule required my table to only registrater certain dob from bithDate column to be inserted or allow. I created the check constraint but i kind of having logical error i not able to spot ...
5
votes
1answer
351 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. ...
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
348 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 ...
3
votes
2answers
154 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; ...
6
votes
3answers
2k 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
177 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
22 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
417 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
445 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
1answer
28 views

Merging 2 queries together

I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before: tdate period staff ...
1
vote
2answers
379 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 ...
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 ...
1
vote
1answer
286 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 ...
4
votes
1answer
254 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 ...
3
votes
2answers
3k 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
2answers
258 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
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 ...
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 ...
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 ...
1
vote
2answers
149 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
197 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
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 ...
2
votes
1answer
851 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
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 ...
1
vote
2answers
313 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
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
85 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
190 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
100 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
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
1answer
187 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
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 ...
1
vote
1answer
414 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 ...