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/).
2
votes
2answers
433 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
286 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
124 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
221 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
170 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
939 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
678 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
203 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
163 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
274 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
310 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
87 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
206 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
411 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
89 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
809 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
291 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
230 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
361 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
857 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
250 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
169 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
938 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
427 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
356 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
362 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
259 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
405 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 ...
2
votes
2answers
940 views
SQL Server : how to drop all constraints on a table in T-SQL
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.
Is there any way to do that with T-SQL? I cannot find any solution ...
5
votes
3answers
2k views
SQL Server 2008 merge statement deadlocking itself
I have the following procedure (SQL Server 2008r2):
create procedure usp_SaveCompanyUserData
@companyId bigint,
@userId bigint,
@dataTable tt_CoUserdata readonly
as
begin
set ...
1
vote
1answer
173 views
SQL Server 2008 R2 - Weekly / Monthly Statistics
I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
4
votes
1answer
622 views
T-SQL: Convert XML to string without self-closing tags
We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so
<BOOKING>
<BOOKINGID>56812</BOOKINGID>
<PERSONCODE />
...
2
votes
2answers
114 views
SQL SELECT Kill FROM problem
$sql = "SELECT Kill FROM tbl_pvporderview";
Problem is that il lend up with : Incorrect syntax near the keyword 'Kill'.
Cuz kill is a TSQL command... any way to bypass it?
I cant change the column ...
1
vote
1answer
95 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
1answer
94 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 | ...
8
votes
2answers
148 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
4
votes
4answers
364 views
T-SQL command to log in as a different user?
Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?
4
votes
4answers
485 views
Speeding up COUNT(*) - WHERE clause slowing query
The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
2
votes
2answers
242 views
Make sure two columns are sorted in the same order
I have a table like this:
CREATE TABLE T (
ID INT NOT NULL IDENTITY PRIMARY KEY,
CreateDateTime datetime2 NOT NULL,
--other columns
)
How can I make sure that ID and CreateDateTime are both ...
1
vote
1answer
225 views
Why is the output of this query being returned like this?
I have a T-SQL query :
select CAST(0 as float(53)),CONVERT(nvarchar(max),CAST(0 as float(53)),128)
the output of which is
0,0.0E0
Can some one explain why it is not 0,0 ? The database is SQL ...
7
votes
1answer
60 views
sysschedules datatype choices
I am Curious.
Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000.
Was it a ...
2
votes
3answers
2k views
Questions about handling NULLs and empty strings in nvarchar and numeric fields
I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I ...
1
vote
1answer
287 views
bitwise operators effecting performance
I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the ...
2
votes
2answers
387 views
Is there any way to speed up a query with 3x UNION on the same large table?
Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?
SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar ...
2
votes
1answer
709 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. ...
8
votes
2answers
431 views
Why does SELECT 1/2 return 0?
As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.
I am using SQL Server 2008.
10
votes
2answers
1k views
Good way to call multiple SQL Server Agent jobs sequentially from one main job?
I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
2
votes
2answers
462 views
Bulk grant execute permissions for scalar functions & stored procedures
We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
