Tagged Questions
13
votes
4answers
14k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
196
votes
4answers
41k views
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
8
votes
3answers
9k 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 ...
1
vote
1answer
841 views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
19
votes
7answers
24k 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 ...
12
votes
2answers
2k 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 ...
3
votes
1answer
114 views
What is the correct way to ensure unique entries in a temporal database design?
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
8
votes
2answers
3k views
Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.
As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
2
votes
1answer
119 views
how to show only changed rows
if want querying the following table :
EventDateTime (DateTime), Host (guid), Flag ( bit)
2013-04-01 05:00, {id-x}, 0
2013-04-01 05:01, {id-x}, 0
2013-04-01 05:02, {id-x}, 0
2013-04-01 05:03, {id-x}, ...
15
votes
3answers
4k views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
13
votes
1answer
5k 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
5answers
1k views
How can I prove that the following T-SQL is bad for performance?
I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to.
A number of the ...
11
votes
5answers
2k views
Does it make sense to use SQL Server's bracket notation in hand written code?
Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.
When I first saw it, I though wow a reincarnation of the somewhat ...
8
votes
1answer
485 views
Generating large strings for test data
I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
5
votes
2answers
140 views
In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?
SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value?
Is it UTC?
Is it local for the server the backup was taken?
Is it local for ...
3
votes
3answers
476 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
3
votes
2answers
5k views
A Script to insert dummy Data in all tables of Database
I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
3
votes
3answers
530 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 ...
2
votes
2answers
225 views
How do I include nulls during comparisons in SQL Server?
So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.
For example, the following query doesn't return rows where value IS NULL ...
2
votes
1answer
140 views
How to improve this queries performance by indexing properly?
Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek.
DECLARE @now DateTime;
DECLARE ...
2
votes
3answers
65 views
Query databases on different servers
How can I create a query that will find all the databases on different servers?
I have four servers, PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or ...
2
votes
1answer
81 views
Getting each status change in a table
I have a table that holds status changes per item. The item can go in and out of a status multiple times.
ItemTable (ItemId int, Status varchar(50), DateOfChange date)
I need to pull the date of ...
2
votes
2answers
739 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
442 views
Design best practices for last tracking with indexed view
My tables structure is below :
TbDoc (ID int , ...)
TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)
I want to have indexed view to get last TbDocActions columns for ...
1
vote
2answers
184 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
votes
1answer
522 views
how cursor implementations are different for each cursor type in sql server
In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
