How to test two datetimes for equality in T-SQL (ignoring their time components)?
How can I test two datetimes (not including their time components) for equality? ...
tsql
COUNT with DATE INTERVALS
I have this table called Table1 as follows: UserID Date 1 01/01/09 1 14/01/09 1 25/01/09 1 01/02/09 1 15/02/09 2 02/02/09 2 15/02/09 I am trying to return a result that counts the number of times between the MIN(Date) and 30 days after the MIN(Date) wh...
How do I drop all foreign-key constraints on a table in Sql Server 2000?
How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL? ...
xml schema collection change - huge performance hit
I'm seeing a huge performance hit in our enterprise application when changing the XML schema collection of a given column on a large table. Simplistically put, we're doing something like this: ALTER TABLE HugeTable ALTER COLUMN CustomFields XML (note: CustomFields was previously bound to XML(CustomFieldsSchemaCollection, but of course...
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed.
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed. What does this mean ? is it that DB is currupted? ...
How to get SQL Server to return a default of 0, if no rows exist?
How can I get a default value of 0 if a sum does not return any rows? Edit: I have edited this post to add a more thorough example (which the previous one didn't encounter) E.g. DECLARE @Item TABLE ( Id int, Price decimal, PricePer decimal ) DECLARE @OrderItem TABLE ( Id int, ItemId int, ChargedPrice nvarchar...
How to create SQL Server Express DB from SQL Server DB
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization stuff and at this point don't know how t...
SQL Query to Get Max Value Based on Different Max Value Given Multiple Records
I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id. Note: The id column is not guaranteed to be sequential, I simplified it for this question. Code: create table temp ( ...
Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon.
Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a ...
How can I extract string 'foo' from a string [email protected]?
select left(emailaddress, len(emailaddress) - charindex('@', emailaddress)) I am getting the result below: foo@ma Please correct the select statement below. I need to do this with tones of email addresses and extract the username ONLY. ...
sql - beginning of hour, month etc..
I know how to get in SQL (SQL Server) the current date, but with the beginning of the day: select dateadd(DAY, datediff(day, 0, getdate()),0) (result:2009-09-17 00:00:00.000) I need to get (in SQL) the current date with the beginning of this hour. For example: 2009-09-17 17:00:00 (I don't care about the exact format) and I need to g...
SQL Server Index question
I have a query that joins 3 tables in SQL Server 2005, but has no Where clause, so I am indexing the fields found in the join statement. If my index is set to Col1,col2,col3 And my join is Tbl1 inner join tbl2 On Tbl1.col3=tbl2.col3 Tbl1.col2=Tbl2.col2 Tbl1.col1=Tbl2.col1 Does the order of the join statement make a difference as com...
Which sql server data type best represents a double in C#?
Is it money, float, real, decimal, _____ ? ...
Update XML node value in SQL Server
I need to update the GroupID field to a 0. I have figured out how to retrieve the value, I am now running into problems updating it. Any help would ge great! <ProblemProfile> <GroupID>-1</GroupID> <LayoutID>1</LayoutID> <MyQueries>false</MyQueries> </ProblemProfile> Declare @Result xml set @Result = convert(xml,(select Profil...
Grabbing most recent row based on column with repeating data?
Table [myRecords] A | B | C | D | E | F | ------------------------------------------------------------------------ 1 | 10 | 1/1/09 | abc | aaa | 111 | 2 | 10 | 1/2/09 | def | bbb | 222 | 3 | 10 | 1/...
SQL 2005 Standard Datatypes
I'm putting together a database of locations for looking up nearest locations for a given address. As I started laying out the table going about my business I wondered what other people were doing and if there was a 'best practices' for some common datatypes. Such as phone numbers, addresses and even latitude and longitude. This is wha...
OPENXML with xmlns:dt
Use OPENXML to get dt element in MSSQL 2005. How can I get xmlns:dt element in xml? For example, get a result set of two rows that list product id and country code. 121403 GBR 121403 USA declare @xmldata xml set @xmldata = '<?xml version="1.0"?> <data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://w...
Database Query Slower with View
I kind of have a feel for why the view is slower: The where clause is probably not applied at the same time. The results do seem to be the same, though. I am not sure what I can do about this, short of not using a view...which is not ideal, as I added the view to avoid code repetition, and I don't want to remove it if it isn't necessar...
Sql Server - top 1 post per member ordered by createdDate
I have a table Posts which has a memberID and createdDate. I need to return the most recent post per member and the posts must be order with most recent at the top. I am not sure how to do this with Sql Server, can anyone help? ...
SQL that can pulls records by a possible three values or none at all
Say you have WIDGETS table: WidgetID int ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown) SizeID int (Lookup values: Small, Med, Big, Large) Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupva...