tsql

T-SQL Problem

I have following table (SQL Server) Table name is LandParcels Blockid ParcelNo Stateorprivate ======================== 11001901 30 Deemana 11001901 35 Deemana 11001901 41 State 11001901 45 State 11001901 110 Private 11001901 111 Private 11001902 1 Deemana 11001902 11 Sta...

SET NOCOUNT OFF or RETURN @@ROWCOUNT?

I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT? ALTER PROCEDURE [dbo].[MembersActivateAccount] @MemberId uniqueidentifier AS BEGIN -- Should I use this? SET NOCOUNT OFF; UPDATE [dbo].Members SET acc...

Does filtering or joining happen first in T-SQL?

I have two tables: Customers(Id, Name, TownId) T Towns(Id, Name) I have an SQL statement like this: SELECT * FROM Customers INNER JOIN Towns ON Towns.Id = Customers.TownId WHERE Customers.Id > 5 What would happen first? Would it filter the Customers table and then join the selected records with Towns table? Would it join all Custom...

Passing an array of values to a stored procedure in SQL 2005

Does T-SQL accomodate for array values as parameters for stored procedures? If so how can this be achieved. ...

How to convert DateTime to a number with a precision greater than days in T-SQL?

Both queries below translates to the same number SELECT CONVERT(bigint,CONVERT(datetime,'2009-06-15 15:00:00')) SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as bigint) Result 39978 39978 The generated number will be different only if the days are different. There is any way to convert the DateTime to a more precise number, a...

What is the equivalent of "FOR XML AUTO" in MySQL? (for having an XML string that represents a row)

Hello! In SQL Server I frequently use "FOR XML AUTO", which allows something like "SELECT id FROM car FOR XML AUTO" to return "<car><id>1</id></car><car><id>2</id></car>". Is there a way to do this in MySQL? What I want is to be able to store this XML in another table so I can have complete log of changes made in the database. The hist...

Grouping in t-sql with latest dates

I have a table like this Event ID | Contract ID | Event date | Amount | ---------------------------------------------- 1 | 1 | 2009-01-01 | 100 | 2 | 1 | 2009-01-02 | 20 | 3 | 1 | 2009-01-03 | 50 | 4 | 2 | 2009-01-01 | 80 | 5 | 2 | 2009-...

T-SQL Time Averaging

I have a table in SQL Server that stores statistics for a piece of hardware, rows in the table represent data for a given second. It contains for example, these columns: timestamp (DateTime) value (int) What I want to do is select the data from the table for a given date/time range but return it in such a way that it averages for a gi...

TSQL: Return row(s) with earliest dates

Given 2 tables called "table1" and "table1_hist" that structurally resemble this: TABLE1 id status date_this_status 1 open 2008-12-12 2 closed 2009-01-01 3 pending 2009-05-05 4 pending 2009-05-06 5 open 2009-06-01 TABLE1_hist id status date_this_status 2 open 2008-12-24 2 pending 2008-12-26 3 open 2009-04-24 4 open...

T-SQL, Cursors, FETCH INTO. How to use SELECT *

I am building a one off query to iterate through a set of joined tables. The select statement is using "SELECT *". Since it's is a table with lots of fields, I don't want to specify each column as a variable. Is there a way to FETCH INTO an array or SET of some sort and just grab the values I want? ...

Recursive Query using CTE in SQL Server 2005

OK, here's what I'm trying to do. I'm using a CTE query in MSSQL2005. The objective of the query is to recurse through Parent child relationships of product categories and return the number of products under each category (this includes any products contained in children categories) My current version only returns the product count for ...

TSQL ORDER-BY with a UNION of disparate datasets

I have a query that UNIONs two somewhat similar datasets, but they both have some columns that are not present in the other -- i.e. the columns have NULL values in the resulting UNION. The purpose of the UNION is to get the data in a friendly format for the software-side. The problem is, I need to ORDER the resulting data using those c...

How can i speed up this Indexed View?

Hi folks, I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie. Update: Proof of Solution at the bottom of this post. Schema This is what it looks like :- CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING ...

How can I change NULL to 0 when getting a single value from a SQL function?

Hi, I have a query that counts the price of all items between two dates. Here is the select statement: SELECT SUM(Price) AS TotalPrice FROM InventoryWHERE (DateAdded BETWEEN @StartDate AND @EndDate) You can assume all of the tables have been set up properly. If I do a select between two dates and there are no items within that date r...

Find lowest common parent in recursive SQL table

Hi, Suppose I have a recursive table (e.g. employees with managers) and a list of size 0..n of ids. How can I find the lowest common parent for these ids? For example, if my table looks like this: Id | ParentId ---|--------- 1 | NULL 2 | 1 3 | 1 4 | 2 5 | 2 6 | 3 7 | 3 8 | 7...

SQL - Query by Date Range

Hello, I have an order header table called "OrderH". In this table there is a column called "OrderDate". I am trying to retrieve the orders with a date within a certain range. I thought that I could accomplish this with the "between" keyword but I am not having any luck. This is this SQL I have been fidgiting with: select * from ...

SQL poor stored procedure execution plan performance - parameter sniffing

I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate I'm having problems whereby if @MyDate is passed in as NULL when the stored ...

Linq .Contains with large set causes TDS error

I've oversimplified this a bit, because I'm looking for a general-purpose answer. Let's say I've got a table setup like this: Parent recno int (unique, pk) date datetime stuff varchar(50) Child parentrecno (int, fk) --- PK sequence (int) --- PK data varchar(50) And in my C# program I've gone through a lot of...

Column listing in SQL

Input: Name Id N1 1 N1 3 N1 4 N1 7 N2 2 N2 1 N2 8 N2 5 N3 4 N3 8 N3 5 N3 3 N4 7 N4 7 N4 7 N4 8 Output: Name1 Name2 Name3 Name4 ---------------------...

Temporary tables in Linq -- Anyone see a problem with this?

In trying to solve: Linq .Contains with large set causes TDS error I think I've stumbled across a solution, and I'd like to see if it's a kosher way of approaching the problem. (short summary) I'd like to linq-join against a list of record id's that aren't (wholly or at least easily) generated in SQL. It's a big list and frequently ...