Do Inserted Records Always Receive Contiguous Identity Values

Consider the following SQL: CREATE TABLE Foo ( ID int IDENTITY(1,1), Data nvarchar(max) ) INSERT INTO Foo (Data) SELECT TOP 1000 Data FROM SomeOtherTable WHERE SomeColumn = @SomeParameter DECLARE @LastID int SET @LastID = SCOPE_IDENTITY() I would like to know if I can depend on the 1000 rows that I inserted into table Foo having […]

How can I structure a query to give me only the rows that match ALL values in a CSV list of IDs in T-SQL

I’ve created a simple example (hopefully much more fun than my actual data) to better express my question: CREATE TABLE SUPER_HERO ( ID INT, NAME VARCHAR(50) ) INSERT INTO SUPER_HERO VALUES (1, ‘Storm’) INSERT INTO SUPER_HERO VALUES (2, ‘Silver Surfer’) INSERT INTO SUPER_HERO VALUES (3, ‘Spider Man’) CREATE TABLE SKILL ( ID INT, NAME VARCHAR(50) […]

How to schedule SSIS package to run as something other than SQL Agent Service Account

In SQL Server 2005, is it possible to schedule an SSIS package to run something other than the SQL Agent Service Account? I’ve got an SSIS package that makes a connection to a database and runs a stored procedure. My criteria is that I will not specify usernames/passwords in a package or package configuration, so […]

Default port for SQL Server

I need to know the default port settings for the following services SQL Server SQL Browser SQL Reporting services SQL Analysis services I neeed to know the port settings for these services for different versions of SQL Server (2000,2005,2008) Also let me know whether the default port setting will change based on sql server versions […]

Create SQL script that create database and tables

I have a SQL database and tables that I would like to replicate in another SQL Server. I would like to create a SQL script that creates the database and tables in a single script. I can create “Create” script using the SQL Management Studio for each case (Database and Tables), but I would like […]

SQL why is SELECT COUNT(*) , MIN(col), MAX(col) faster then SELECT MIN(col), MAX(col)

We’re seeing a huge difference between these queries. The slow query SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate FROM table WHERE status = ‘OK’ AND fk = 4193 Table ‘table’. Scan count 2, logical reads 2458969, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL […]

How to ensure the SQL is able to read all XML tag data

I have the following XML data in a SQL table column: <root> <Physicians> <name></name> <picture></picture> <gender></gender> <langAccept>English</langAccept> <langAccept>Spanish</langAccept> (can appear more times) <insAccept>Aetna</insAccept> <insAccept>BCBS</insAccept> (can appear more times) <specialty></specialty> <specialty2></specialty2> <specialty3></specialty3> </Physicians> </root> The langAccept and insAccept can appear multiple times, and there is no way to know how many times. I have the following […]

NOLOCK vs. Transaction Isolation Level

What’s the difference between using “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” and NOLOCK? Is one better than the other?

SQL Server 2005 recursive query with loops in data – is it possible?

I’ve got a standard boss/subordinate employee table. I need to select a boss (specified by ID) and all his subordinates (and their subrodinates, etc). Unfortunately the real world data has some loops in it (for example, both company owners have each other set as their boss). The simple recursive query with a CTE chokes on […]

Pivot in sql server

Yes I’ve tried the code. My requirement is that user inputs Year and Month & prices are shown date-wise in columns for that year and month, with first column as CompetitorID. I want my result like: Competitors | day1 | day2 | day3 | day4 …………..|day31 ================================================================ competitor 1| Price | Price | price | […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.