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

learn more… | top users | synonyms

3
votes
1answer
493 views

Does T-SQL MOVE actually move the database, or just copy?

I'm a bit confused over the T-SQL command MOVE. I have a statement saying: MOVE N'Prod_Database_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf' ...
3
votes
2answers
281 views

Physical Server or a Virtual Machine SQL Server?

Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?
3
votes
2answers
274 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
3
votes
3answers
1k views

Conditional WHERE Clause in a Table Valued Function

I have a table valued function CREATE FUNCTION [dbo].[DateRange] ( @StartDate date, @EndDate date, @Location varchar(25), @Device varchar(25) ) ...
3
votes
3answers
672 views

Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included. Is ...
3
votes
1answer
672 views

LIKE character length limitation

The background to this question can be found here. The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
3
votes
1answer
160 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
3
votes
2answers
303 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub sproc based on passed param. if ...
3
votes
2answers
753 views

MS SQL : Use Computed value to Compute other values

I'm having problem with generating results which are computed from data that exists in database. This is the case I have Coupons that can be amount or percent, if percent is 0 then amount is used ...
3
votes
2answers
69 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 ...
3
votes
1answer
1k views

Mapping of multiple server logins to one database user

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology). Is it possible to log multiple ...
3
votes
1answer
413 views

How to replace “set rowcount”

Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release: http://technet.microsoft.com/en-us/library/ms188774.aspx We have some legacy code ...
3
votes
4answers
441 views

How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
3
votes
2answers
183 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 ...
3
votes
2answers
4k views

Create job to send alert using sp_send_dbmail when table value greater than x

PROBLEM The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached. TABLE CREATE CREATE ...
3
votes
2answers
320 views

What do these statements mean in the MS β exam 70-461 “skills measured” list?

I have registered for the upcoming beta exam 70-461 entitled Querying Microsoft SQL Server 2012. I've been reading through the exam overview and found a bunch of descriptions I'm not exactly sure ...
3
votes
2answers
502 views

Tool to create a TSQL script from a SQL Server 2008 trace file

Are there any tools available to create an executable tsql script from a SQL Server trace file? Is there an easy manual way?
3
votes
1answer
77 views

Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in ...
3
votes
1answer
114 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
3
votes
2answers
3k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
3
votes
1answer
108 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
3
votes
1answer
806 views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
3
votes
2answers
369 views

can I convert an integer to base-2 without a function

Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from ...
3
votes
2answers
1k views

Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. ...
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
1answer
139 views

When to use With Recompile

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc.. Sometimes there are cases that it may help, but OPTION(RECOMPILE) ...
3
votes
1answer
142 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
1answer
1k views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
3
votes
1answer
288 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
3
votes
2answers
214 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
3
votes
1answer
470 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
751 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: ...
3
votes
1answer
104 views

Relationship with a default value

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title. Here is my current relationship CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL, Name ...
3
votes
3answers
594 views

t-sql - combinatorics

I'm trying to find all possible character combinations in a variable length string. For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--' I think that essentially I need ...
3
votes
4answers
599 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
3
votes
1answer
122 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
3
votes
1answer
414 views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
3
votes
1answer
52 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 ...
3
votes
1answer
328 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
3
votes
1answer
303 views

How are foreign keys resolved when creating tables in batch?

I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
3
votes
1answer
2k views

sp_execute expects parameter '@handle' of type 'int'

I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
3
votes
2answers
131 views

When did a specific database come online?

How can I determine when the state of a specific database switched to online? Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats ...
3
votes
2answers
5k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
3
votes
1answer
2k views

Script to query multiple instances

SQL Server - What is the best method to run the same query over multiple databases on multiple instances? for example we have many application databases, with the same schema that need to have ...
3
votes
1answer
252 views

Strange behaviours with SET options in SSMS

I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code: ensures that the SET options are as they should be creates a table ...
3
votes
1answer
195 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
3
votes
1answer
681 views

Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

For a TSQL report, I have a one-to-many parent-child relationship. I want to report both parent and child relationships in the same report, like so, with the parent columns on the left and child ...
3
votes
1answer
2k views

How to remove xmlns from child elements with FOR XML

I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely: - http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document - A custom one that ...
3
votes
1answer
703 views

New foreign key and merge replication

We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this ...
3
votes
1answer
53 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...