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

Should I run a delete first before I execute index rebuild to a large table about 15 Million records

I need help for a better approach on how to approach this. Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...
0
votes
2answers
184 views

Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012. I am doing it for the first time and every thing is fine i.e. all data from a particular table ...
0
votes
1answer
7k views

Cannot find either column “dbo” or the user-defined function or aggregate “dbo.fnDiscountPrice”, or the name is ambiguous

i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a ...
1
vote
2answers
723 views

Metric to standard unit conversion in SQL Server

I am giving simplified version of my two table which are as follow. tblRecords ID unit val --- --- --- 1 KV 3 1 BAR 4 2 KV 7 2 KG 5 3 C 37 tblUnit unit ...
1
vote
1answer
255 views

To use a cursor or something else in my while loop?

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select ...
5
votes
1answer
2k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
1
vote
2answers
348 views

SP or query to dump “password policy” checkbox for all logins

sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
1
vote
2answers
966 views

Should I load the data into Temp table or keep calling it from the normal table? [closed]

I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two ...
2
votes
1answer
250 views

Should you use the schema name in WHERE, ON, and other clauses?

I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other ...
0
votes
1answer
387 views

Multibyte replace not working in SQL Server

Why does one work and the other doesn't? SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI') SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI') My guess would be that in the ...
5
votes
1answer
433 views

Passing a literal ? to sp_MSforeachdb

I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
0
votes
2answers
438 views

SQL Server compact- Query that return 2 constant columns even if query didn't return data

Please look at the following simple query: Select CustomerID From Customers c Where c.CustomerId = "1234" I want to write similar query that does the following: Always return 2 columns with ...
1
vote
1answer
135 views

How to write this query in SQL Server Compact in more elegant way?

The query is : Select Case AStatus When 1 then 'Success' When 0 then 'Faliure' END, Case AStatus When 1 then 'AStatus' ...
3
votes
1answer
4k views

Nonclustered Index still doing index scan

I have this simple query. SELECT a.RecordID SubmissionID, a.[DateSubmitted], a.[FinalAction], a.[FinalActionReason], a.[FinalActionDate], b.GradeSubmissionListID, b.[Action], ...
0
votes
1answer
410 views

Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF

In my question over at Pull every other row, starting a 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the ...
2
votes
2answers
572 views

Pull every other row, starting at 0, and concatenate them together

I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other ...
4
votes
1answer
13k views

Split/explode comma delimited string field into SQL query

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216' and I want to use it to search IN this field: SELECT * FROM table1 WHERE id IN (id_list) id is integer id_list is varchar/text ...
3
votes
3answers
209 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
0
votes
1answer
258 views

Can this query be optimized further?

I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
3
votes
1answer
760 views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
3
votes
1answer
1k views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...
1
vote
0answers
266 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
3
votes
2answers
368 views

Grouping Selecting unique values in a single column [duplicate]

Here is my table ID Field1 Field2 Field3 Field5 Field7 357 357 2013-03-07 08:02:02:275 t02 bBCD00103RG 365 365 2013-03-07 08:02:05:307 t02 bR U00103w 374 ...
0
votes
0answers
247 views

Getting Request Timeout from Store Procedure?

Only sometimes, I get Request Time-out(means my SP is not responding within a specfied time-period which is 90 seconds). Here is my SP. ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] ...
1
vote
1answer
224 views

How to insert and or update a variable number of records and prevent concurrency issues

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be ...
0
votes
1answer
270 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
1
vote
1answer
956 views

Getting collation confict in IF statment and need help resolving it please

A SQL script I am writing is generating the error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation. I ...
1
vote
1answer
1k views

How to find column with all same repeating character

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why ...
-3
votes
1answer
94 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
2
votes
1answer
129 views

Query does not use index when selecting all records [closed]

This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan. I have setup an index on a specific table: CREATE ...
0
votes
1answer
4k views

Uncommitable T-SQL transaction after trigger applied to table

Good morning, I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record ...
1
vote
2answers
136 views

Improving performance of query with `WHERE <> ''`

I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case] Is there any way to improve the performance of the query SELECT * FROM myTable WHERE ...
0
votes
1answer
158 views

Can I design this database better? [closed]

I have the following database design: Script to reproduce: /* DROP TABLE [dbo].[feedback] DROP TABLE [dbo].[action] DROP TABLE [dbo].[download] DROP TABLE [dbo].[user] DROP ...
1
vote
1answer
337 views

Simple update query balloons tempdb

I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is ...
1
vote
1answer
623 views

Check for column in backupset table

Created a script to show last backup locations, sizes and dates SELECT DatabaseName = x.database_name, LastBackupDate = x.backup_finish_date, BackupSizeKB = x.backup_size, ...
0
votes
1answer
133 views

How do I setup a foreign key relationship based on two columns?

I have the following two tables: CREATE TABLE [dbo].[company] ( [CompanyID] [int] NOT NULL , [CompanyName] [varchar](150) NULL , [DepartmentID] [int] NOT NULL , ...
0
votes
1answer
268 views

T-SQL Gurus, I need help optimising these queries for performance

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids In a production environment the table ...
1
vote
0answers
183 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
2
votes
1answer
978 views

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) ...
1
vote
2answers
149 views

Optimize Query of Count Less Than

I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, ...
1
vote
1answer
171 views

How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it. The SP filters out all the calculated columns and other stuff that stops you doing ...
2
votes
1answer
2k views

What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n) So I wrote the following queries to just check (as I'm learning T-SQL) ...
3
votes
1answer
56 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...
1
vote
2answers
481 views

Where can I find scenarios to work through T-SQL [closed]

I've been working through the exam guides for the new 70-461 MCSE data exams and i'm at a point where I want to practice my T-SQL. Does anyone know of any good resources whereby scenarios are posed ...
0
votes
3answers
542 views

Dividing in SQL

can anyone explain to me why this query returns 0.000? SELECT CAST(20/1024 AS NUMERIC(10,3)) I am just trying to do a simple conversion of MB to GB Thanks
0
votes
1answer
834 views

Extract time portion of Datetime2(7) in hhmmssfff format

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this: DECLARE @mdate AS DATETIME2(7) SET @mdate = '2012-03-15 10:13:27.5437431' SELECT ...
3
votes
1answer
238 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
0
votes
1answer
1k views

LEFT vs CHARINDEX for matching the start of a string in T-SQL

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing ...
-1
votes
1answer
693 views

Totals Column - Blank if Zero or NULL

A report currently computes a "totals" column like so: ,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) + ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0 THEN '' ...