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/).
5
votes
1answer
400 views
Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?
When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
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 ...
4
votes
2answers
80 views
Difference Between SP with BEGIN/END and without BEGIN/END
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
4
votes
4answers
496 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 ...
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
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 ...
4
votes
1answer
705 views
SELECT INTO creates a new table?
I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
4
votes
1answer
80 views
Is it “legal” to CREATE and DROP #SomeTable more than once?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:
CREATE TABLE #WidgetSetting
...
4
votes
1answer
134 views
Is it possible in SQL Server to change default “order by” direction from asc to desc?
This question came up in a discussion at work... given the following T-SQL statement:
select firstname, lastname from users order by lastname;
the default sort order on the order by clause is asc. ...
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 ...
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 ...
4
votes
3answers
211 views
Copy 8 Million Records
I have a table in a local MS SQL Server database that has 72 columns and over 8 million records.
Most of the columns are floats except for the primary key which is an INT Identity column.
The table ...
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 ...
4
votes
2answers
177 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
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 ...
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
1answer
534 views
How to get text for default language
I have so tables:
and so data at Language table:
and so data at Text table:
I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
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 ...
4
votes
4answers
408 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
1answer
3k views
How can I strip non-numeric characters out of a string?
Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.
One ...
4
votes
1answer
454 views
Using FLOATs with RAISERROR
I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
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
1answer
392 views
T-sql: Select most specific match from table
Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem.
Here's the scenario:
Users can set up service instructions to be attached to a work order ...
4
votes
1answer
674 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 />
...
3
votes
3answers
385 views
Choosing the right algorithm in HashBytes function
We need to create hash value of nvarchar data for comparison purposes.
There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
3
votes
2answers
6k views
Writing select result to a csv file
We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.
I ...
3
votes
3answers
950 views
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server.
create table #table_name
(
column1 int,
column2 varchar(200)
...
)
insert into #table_name
execute ...
3
votes
2answers
112 views
Find top by values by percents using range in SQL Server
What will be easier way to group records by range of percents.
Here what I try to accomplish
Select top percents between 50 and 75 * from myTable order by PKI
Here is way I did this but I have ...
3
votes
2answers
2k views
How can I get a list of column names and types from a resultset?
Say I get a resultset back from the following query:
select *
from sys.database_files;
For any given resultset, I want to be able to query the column names and types so I can then create tables to ...
3
votes
2answers
334 views
How can I create different sets of sequential values based on another column in a table?
I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.
CREATE TABLE Notes (
Id int IDENTITY(1,1) NOT ...
3
votes
1answer
313 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
3answers
113 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
1answer
186 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
100 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
3k views
CASE ORDER BY with multiple columns, and different sort options
I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.
@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column ...
3
votes
2answers
180 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
633 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
308 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
102 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
2answers
133 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
99 views
Update strings for all cmdexec Jobs
Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?
We'd like to change our logging location, and in principle I'd think this could be ...
3
votes
2answers
256 views
Subquery in the VALUES clause
Here's a real-world schema:
CREATE TABLE [dbo].[Setting]
(
[ID] [BIGINT] NOT NULL,
[Module] [NVARCHAR](400) NOT NULL,
[Property] [NVARCHAR](400) NOT NULL,
[Value] ...
3
votes
1answer
58 views
SP for Restoring Database and performing other tasks on it
I am creating a stored procedure for our QA dept that will do the following:
Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
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
...
3
votes
4answers
409 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
2answers
220 views
Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.
We need ...
3
votes
1answer
249 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
2answers
87 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
3answers
3k views
Add a new Column and define its position in a table
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE ...
3
votes
3answers
353 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 ...
