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
1answer
20 views

SQL weight multiplication

Hi all i have two columns in a table : +--------------------+----------------+ | Units/Pk | PackWeight/Vol | +--------------------+----------------+ | 1.0000000000000000 | 150g | ...
0
votes
1answer
32 views

Table size is not changed after new column is added

I am trying to check how the table size is going to be increased after adding new column with default values. I use the following default values: NULL, ''(empty string) and some text(some random ...
3
votes
0answers
31 views

How to format T-SQL identifiers in camel case

A number of tools exist which are designed to facilitate formatting the T-SQL in an inherited script or stored procedure to match one's personal preferences. One feature I would like to see in such ...
0
votes
1answer
17 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Lagitude and Longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
0
votes
0answers
18 views

delete millions of rows from multiple tables

Hi we have to delete between 40 and 55% of records from several tables that have between 40 million and 100 million rows. Is it better to copy the rows we want to keep to a new table, drop the source ...
0
votes
1answer
62 views

How to increase the performance of this 3 minute reporting query

We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ...
4
votes
2answers
65 views

How to change default database to all logins?

I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login ...
0
votes
1answer
37 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
1
vote
2answers
55 views

T-SQL — Why do ALTER and UPDATE statement only run when separated into their own batch?

I have update and alter statements that are only running when they are put into they're own batch. Below is my shortened version. It'd probably be a good idea to skip the code and read my question ...
4
votes
1answer
128 views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL db. If the row already exists in the table, we update that ...
1
vote
0answers
18 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
0
votes
0answers
32 views

Export a varbinary column to file system using existing authentication

I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use ...
1
vote
2answers
36 views

What is the correct query set up to access the columns in table being passed as parameter?

I am trying to use table-valued parameter to bulk upsert some data into my table. I have the following stored procedure. This setup seems to agree with the intellisense. I am not getting any errors ...
0
votes
0answers
10 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 ...
0
votes
0answers
37 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
84 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
35 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
22 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
47 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
56 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
65 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
193 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
123 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
45 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
275 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 -> ...
12
votes
2answers
247 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
88 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
156 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
36 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; ...
4
votes
3answers
506 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
44 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
20 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
67 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
110 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
62 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 ...
31
votes
6answers
1k 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
78 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
110 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
199 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
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 ...
1
vote
2answers
270 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
63 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
68 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
244 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
140 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
81 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
126 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
46 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
86 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 ...