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/).
10
votes
1answer
341 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 ...
7
votes
1answer
244 views
Variable for element name
I have this tSQL code which works OK:
SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
FROM [dnn].[dbo].[EventLog2] c
cross apply ...
3
votes
1answer
177 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
125 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
71 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
75 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
50 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
194 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
292 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
92 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
100 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 ...
0
votes
3answers
253 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 ...
2
votes
1answer
179 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
...
6
votes
4answers
257 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 ...
3
votes
2answers
5k 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 ...
1
vote
2answers
90 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 ...
3
votes
1answer
235 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
55 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
118 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
51 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
103 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
104 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
1answer
608 views
SQL Server 2012 is putting [brackets] around Table and Column Names
Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
4
votes
2answers
176 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 ...
2
votes
3answers
388 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 ...
0
votes
0answers
110 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 ...
11
votes
2answers
998 views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
0
votes
1answer
440 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
578 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
67 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
310 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
185 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
402 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
309 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
243 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
171 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
126 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
155 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
551 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
68 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 ...
0
votes
1answer
221 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
285 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
833 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
87 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
11k 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
265 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
124 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
395 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
425 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 ...
