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/).
9
votes
3answers
193 views
Unique Identifier with Extra Characters Still Matching in Select
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.
For ...
0
votes
1answer
33 views
Copy Permission From Windows Based user To Local user
I want to know if it is possible to copy the permissions of a windows user to a locally created user on SQL server?
0
votes
2answers
74 views
Need a way to query a table, and JOIN it with the TOP 1 related record from an other table
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT ...
2
votes
2answers
94 views
Why do I need to use a sub query to filter down a grouped select?
If I do this --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name
...
2
votes
2answers
130 views
Help with tricky update statement
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE
...
2
votes
2answers
223 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 ...
0
votes
2answers
157 views
Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP
I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.
I have a web based form with multiple ...
6
votes
2answers
282 views
Is there any difference between putting a column alias at the start or the end of the column definition?
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get ...
3
votes
2answers
255 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
3answers
378 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 ...
10
votes
1answer
476 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
-1
votes
1answer
175 views
How to set the timeoffset in a Datetimeoffset column without changing the date part
Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well?
In the post below, we can see the SWITCHOFFSET will ...
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 ...
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. ...
0
votes
1answer
84 views
Changing data type from DATE to CHAR(10) with a query
I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.
I ...
0
votes
1answer
82 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
2
votes
1answer
54 views
Synchronize Queries Retrieving Sessions And Locks From DMVs
I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
3
votes
2answers
219 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 ...
0
votes
1answer
379 views
how to return table on sql function
I am very new to sql create function
i wanted to check data column value at table
the purpose is to return full table where data is null , <0 , >1
the problem is i am trying make data is ...
1
vote
1answer
63 views
Reference the database from a stored proc?
I'm using this T-SQL to get index stats (edited for brevity):
SET @db_id = DB_ID(N'TestDB');
SET @object_id = OBJECT_ID(N'TestDB.dbo.Users');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, ...
7
votes
1answer
94 views
Trouble with SQL and aggregates
With this query:
SELECT SUM(qty) AS sumQty,
cnetprodid
FROM quoteitem
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
...
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 ...
2
votes
1answer
197 views
return percentage of records based on value in column
I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below
...
0
votes
3answers
368 views
Non-Clustered-Index on a temporary table
I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is ...
6
votes
4answers
282 views
Returning rows where all non-NULL values are equal
Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:
WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL
this ...
1
vote
2answers
97 views
How to link each column in sql?
I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
1
vote
1answer
71 views
sp_startpublication_snapshot Parameter(s)
I am creating a stored procedure that:
Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
(If specified in the SP's parameter) Creates ...
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
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 ...
0
votes
0answers
134 views
RAND in T-sql function
Can any one tell why t-sql not allowing using RAND function in side a function
SELECT CAST (RAND() * 10000000 as varchar(20))
the error is
Invalid use of a side-effecting operator 'rand' ...
1
vote
1answer
52 views
Does implicit casting negatively affect index usage?
I have a query similar to the following:
SELECT id FROM MyTable
WHERE eventCount > eventCutoff * eventFactor
eventCount and eventCutoff are defined as a smallint, and eventFactor as a float.
I ...
0
votes
1answer
110 views
Having trouble with a T-SQL scalar variable during a create table [closed]
I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table. Can anyone tell me what is wrong with this script?
-- T-SQL MSSQL
SET NOCOUNT ON
DECLARE @tt = ...
3
votes
2answers
111 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 ...
2
votes
3answers
557 views
Inserting rows into other table whilst preserving IDENTITY
I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
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 ...
0
votes
0answers
118 views
SQL Server 2008 R2 Database Diagram Error Code
I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
5
votes
2answers
251 views
T-SQL Purpose of MAX in this Group By Query
I have run into some pre-existing SQL that I'm having a hard time uderstanding.
SELECT
MAX(I.Symbol) Symbol
, MAX(I.Ticker) CUSIP
, MAX(I.Name) Name
, SUM(H.Quantity) TotalQuantity
, ...
0
votes
1answer
452 views
How to split the comma separated list in the stored procedure
I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
6
votes
1answer
313 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
6
votes
1answer
757 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
336 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
207 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 ...
1
vote
1answer
347 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
1answer
149 views
error in Execute SQL Task with parameter
I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
-3
votes
3answers
478 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
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
192 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 ...
