tsql

datetime and timespan arithmetic in TSQL via HQL

I need to create an HQL where clause which has the form: where tbl1.DateTimeField + tbl2.TimeSpanField >= :someDateTimeParameter The DateTimeField is of type DateTime The TimeSpanField is of type BigInt (is this the best option?) The someDateTimeParameter is a DateTime writing the HQL query as above translates almost exactly into T...

Get the SUM of TIME datatypes (MSSQL08) from a table

I have the following table of TIME datatypes clarification: I am representing hours/mins/seconds of videos and want to figure out how long the videos will be. runtime ---------------- 01:27:19.0000000 01:26:09.0000000 01:00:56.0000000 01:09:59.0000000 01:25:43.0000000 01:16:01.0000000 01:27:12.0000000 01:22:00.0000000 01:17:47.0000000 ...

Insert into one column data from two columns

Table A Col1 Col2 101 102 101 103 102 104 104 105 Table B Col1 101 102 103 104 105 I want to take data from Table A and insert it into Table B as a Distinct value in one query so INSERT INTO TableB (Col1) (SELECT ...) Any ideas? ...

Is using MS SQL Identity good practice?

Is using MS SQL Identity good practice in enterprise applications? Isn't it make difficulties in creating business logic, and migrating database from one to another? ...

SQL Server 2005 restore one schema only

I am pretty sure this isn't possible.... We have a database with several schemas. Each schema belongs to a different user. One user was asking "if I find out I made a whole load of errors would it be possible to revert to the state my data was in yesterday". Obviously we could restore the whole database, but that would restore the other...

Ratio of matching rows per group

I'd like to calculate the ratio of items in a group that fulfil certain criteria out of the total number of items in that group. I've already solved this but am curious to know if my solution is optimal, as the query takes a problematically long time over my large (10m+) dataset. Here is what I have in its simplest form: create table #...

sort distinct date column

Hi Experts, I need distinct year and month from date column which would be sorted by same column. I have date coulmn with values like (YYYY/MM/DD) 2007/11/7 2007/1/8 2007/11/4 2007/12/3 2008/10/4 2009/11/5 2008/5/16 after having query, it should be 2007/1/1 2007/11/1 2007/12/1 2008/5/1 2008/10/1 2009/11/1 This doesn't seems to ...

Handling identity columns in an "Insert Into TABLE Values()" statement?

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following? Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all. ...

Writing Recursive StoredProcedures

Basically what i want in my stored procedure is to return a list of tables, store this list in a variable; i need to go through every item in my list to recursively call this storedprocedure. In the end i need an overall listOfTables built up of this recursion. Any help would be most appreciated ...

SQL Server Index on VarChar(50) - Is this fulltext? Or what?

What exactly happens when I create an index in my SQL Server 2008 DB on a VarChar column. This is obviously a text column. Will it be automatically fulltext indexed or does it create a different kind of index? ...

SQL: View against Table - are queries against the View still using Table Indexes?

I'm using a view ("UsersActive") against my table ("Users"). The view has only one filter, it checks if DateTime Users.DeletedOn is NULL; it basically contains all Users that are not deleted. If I now execute Linq queries against the view instead of the table, will they still use the table indexes or do I need to create special indexes ...

C#: Access SQL view through generic RepositoryBase class?

I'm using a RepositoryBase<T> base class as the foundation for my individual repositories (e.g. UserRepository). It simplifies things like adding new entities etc. for example: public IQueryable<T> SelectAll() { return db.GetTable<T>().AsQueryable<T>(); } Now I've added a view "UserActive" in my database. Is there any way to...

When using Linq, is DbNull equivalent to Null?

This is not about DBNull vs Null. I understand the difference. What I would like to know is if I am using Linq, say to access a User.EmailAddress, then checking User.EmailAddress == null is the same as User.EmailAddress == DBNull correct? My reasoning is that the absence of data in the database results into Linq not generating an objec...

Implementing secure, unique "single-use" activation URLs in ASP.NET (C#)

Hi, I have a scenario inwhich users of a site I am building need the ability to enter some basic information into a webform without having to logon. The site is being developed with ASP.NET/C# and is using MSSQL 2005 for its relational data. The users will be sent an email from the site, providing them a unique link to enter the speci...

SQL Pivot on subset

I have the following result set: Type | Method | Amount Type1 Cash Amount Type1 Check Amount Type2 Cash Amount Type2 Check Amount Type3 Cash Amount Type3 Check Amount And I want to make it look like this: Type | Cash | Check Type1 Amount Amount Type2 Amount Amount Type3 Amount Amount How can ...

Using TSQL, can I increment a CHAR(1) column by one and use it in a LEFT OUTER JOIN without a CASE statement?

This question is similar to my last question. Except this time I'm using letters rather than 6 digit integers. I want to find the out of sequence "letters". Let's say I have the following data: id | Date | Letter ----------------------------- 01 | 5/1/2009 | X 02 | 5/1/2009 | Y 03 | 5/1/2009 | Z 04 | 5/1/2009 | A 05 | 5/1/2009 | B 06 ...

How to pronounce @@error

How do you refer to something like @@error (T-SQL) when speaking? Is it like "at at error" or do you just say what it actually holds/represents (the error number)? ...

SQL Server Process Queue Race Condition

I have an order queue that is accessed by multiple order processors through a stored procedure. Each processor passes in a unique ID which is used to lock the next 20 orders for its own use. The stored procedure then returns these records to the order processor to be acted upon. There are cases where multiple processors are able to ret...

Reclaim space in SQL 2005 table after changing datatype?

I inhereited a database with tables with many nvarchar columns. The tables were getting fairly large, and I decided to change the datatypes to varchar to cut storage because we do not use international characters. The "data space" on the table (Right-click, then "Properties") has not changed. However, if I copy this table into a new tabl...

.NET and TSQL "For Xml Auto" Splits XML Result

I'm using some TSQL with 'For XML Auto'. If I run the TSQL in management studio, I get a single row. If I fill the DataTable using the DataAdapter.Fill() command, I get two results. It appears that the results are split in the middle of one of the tags. It is not a huge XML file, in fact its quite small (perhaps 20 lines), so size is...