Transact-SQL (t-sql) is a dialect of SQL used by Micrsoft's [SQL Server](http://www.microsoft.com/sqlserver/en/us/default.aspx) and SAP's [Sybase](http://www.sybase.com/).
7
votes
1answer
767 views
MERGE a subset of the target table
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:
It ...
4
votes
1answer
69 views
Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?
Expressions like NOT IN (1,2,3) are valid in a where clause:
SELECT foo FROM bar WHERE
((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22))
However if I want to move an elaborate expression ...
8
votes
1answer
338 views
Forcing an index spool
I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear.
However, I end up with a ...
2
votes
1answer
209 views
exist (select * from my table)
My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.
Query (1):
IF ( SELECT COUNT(UnitTrustCounterId)
FROM ...
-3
votes
3answers
483 views
Join tables on a partial string match
input: I have a table containing (any number of) user-supplied strings:
╔═══════╗
║ Value ║
╠═══════╣
║ To ║
║ An ║
╚═══════╝
And a table of user details:
╔════════╦══════════╗
║ UserID ║ ...
1
vote
1answer
348 views
SQL Server Full-Text Indexer with stoplists/stopwords
As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me.
This ...
1
vote
2answers
261 views
Bitwise operations in TSQL
Take the following code:
Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)
Now, shouldn't the output of this code be 5 ?
...
4
votes
3answers
194 views
Dynamic database reference in T-SQL scripts
We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively).
Inside foo family of databases there's a view that should ...
4
votes
1answer
129 views
T-SQL and GPS co-ordinates
I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones.
I've read about STDistance on MSDN, but it only appears to exist in SQL ...
2
votes
2answers
160 views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
1
vote
2answers
714 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 ...
0
votes
1answer
69 views
modify T-SQL to get results
I am an amateur.
I am trying the following
select * from [dbo].[AccessFeeDetail]
where LastLoginDate >= '10/15/2012 00:00:00.000', jobname not like 'Uptest_%'
I am making a mistake on where ...
1
vote
1answer
228 views
Stuff function in T-SQL
select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
from sysjobhistory
The query I have ...
1
vote
2answers
321 views
Which is better for long term database scalability: adding columns or having key/value store
For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:
to add new columns to settings table every time new ...
1
vote
2answers
946 views
how to calculate difference of first row and last row fields value in each group
I have table with structure like this:
+-------+------------------+
| Value | Date |
+-------+------------------+
| 10 | 10/10/2010 10:00 |
| 11 | 10/10/2010 10:15 |
| 15 | ...
1
vote
0answers
91 views
TSQL Trigger Incorrect Syntax
Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G:
INSERT INTO aTable
(
tTypeId,
opId,
inserted.Id
)
SELECT
3,
0,
inserted.Id
...
15
votes
7answers
12k views
How can I tell if a SQL Server database is still being used?
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum ...
5
votes
2answers
290 views
T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs
I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
0
votes
3answers
134 views
Pivoting row contents to produce one dimensional result
I have a table with a varchar column containing pipe-delimited values
For example:
Row 1 Column 1 = a|b|e|gg|foo
Row 2 Column 1 = oV|foo|do
Row 3 Column 1 = boop
How can I query this to return ...
5
votes
4answers
421 views
Create a plan guide to cache (lazy spool) CTE result
I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
2
votes
2answers
476 views
Merge two records in the same table, keeping foreign key relationships intact for both
I have a table, called SITES, that has three columns, lets say it looks like this:
ID Name Path
1 Google http://www.google.com/
2 Microsoft http://www.microsoft.com/
I also have ...
2
votes
2answers
357 views
How to map another server through SQL Server Management Studio
I am trying to map another server by giving the command
EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'
I got an error with this network path not ...
2
votes
1answer
135 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 ...
0
votes
1answer
238 views
Query performance and join hints, plan cost, and duration
Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.
Background
I have a reporting database using ...
4
votes
1answer
187 views
Calculating Median Value within a Group By
I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is ...
4
votes
3answers
1k views
Restrict update on certain columns. Only allow stored procedure to update those columns
I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
3
votes
1answer
756 views
How can I create a stored procedure using xp_fixeddrives?
I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008
ALTER PROCEDURE freespace
AS
...
1
vote
0answers
213 views
Query tuning help needed (Hash Match and Table Scans)
I need help optimizing the following query (returning ~8k rows):
SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
FROM (
...
2
votes
2answers
169 views
T-SQL Naming Standard
Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL?
If there isn't one from Microsoft then is there one with broad acceptance?
1
vote
1answer
297 views
bulk insert from file
I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.
I would like to use BULK INSERT, but I have no access to the db server file system.
Questions
is there a ...
0
votes
6answers
325 views
Equivalent of CHARINDEX OR IN for ints
I want the equivalent of this for ints.
--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0
...
0
votes
2answers
88 views
Print variable of same datatype
I am looking for to print two variables of same datatype I have something like this
declare @one int ,@two int
set @one=1
set @two=2
print @one+@two
obviously it will give a result 3 but
I need ...
4
votes
2answers
215 views
T-SQL Syntax error copying views dynamically
I'm creating a stored procedure to copy all views from one database to another using the following code.
ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB ...
1
vote
1answer
508 views
To use or not use ISNULL(NULLIF(Field1, ''))?
In the past I've seen use of something such as
SELECT ISNULL(NULLIF(Field1, ''), 'NewValue')
to tersely get a fallback value.
However, since the advent of CASE in TSQL, we've favored something ...
5
votes
1answer
93 views
Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?
The Setup:
Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.
Now the Transaction Log grows so quickly during the ...
1
vote
2answers
867 views
Options for keeping two SQL Server Compact databases in sync?
Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL ...
4
votes
2answers
300 views
Restore all applicable files in a backup device in T-SQL
I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
4
votes
3answers
259 views
Can I change table structure in a transaction and then roll it back if there is an error?
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
1
vote
1answer
375 views
Trying to Build TreeView Query in T-SQL
I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2.
I would like to build a query that I can use to populate a ...
7
votes
3answers
889 views
Using Row_Number to find consecutive row count
I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row
If my data looks like this
724
727
728
733
735
737
743
747
...
5
votes
1answer
271 views
Query performance while using sqlcmd [duplicate]
Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS
I have a number of scripts that query the data from different tables and consequently update them. I have ...
5
votes
3answers
1k views
RETURN statement before DEALLOCATE CURSOR
Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure
Consider this example:
CREATE PROCEDURE [dbo].[test]
@ReturnEarly BIT = 0
AS
BEGIN
SET NOCOUNT ...
1
vote
1answer
184 views
Using sys.dm_os_performance_counters and DIFF to display logins per second
PROBLEM
Running my script below i can see the number of logins as an accumulated number to my instance:
SELECT cntr_value AS [LoginsPerSec]
FROM sys.dm_os_performance_counters
WHERE
...
1
vote
2answers
1k views
Cast NTEXT to INT for calculation and comparison
I need to convert an NTEXT column to an INT so I can perform some checks on it.
The first check would be is value = 0, and the second would be if it was an even or an odd number. I've got this ...
1
vote
1answer
434 views
T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship
I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!):
--------------------------------
shipments ...
4
votes
2answers
387 views
Query two SQL Server databases in same function but on two different servers
Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases. Is it possible to query both ...
2
votes
1answer
368 views
How to design database for tree view with an infinite depth
I am trying to combine C# and SQL2005 for an application which need to have tree view control.
One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
2
votes
1answer
78 views
How to get dependencies order
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
6
votes
1answer
273 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
5
votes
3answers
430 views
Total of orders open on a given date for each date in a date range
This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.
I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
