How can I compare two variables/columns of type NTEXT
I know I can convert the variables to nvarchar(max) first and then compare with the = or <> operators. But is this the right way? ...
tsql
T-SQL Query Returning Items It Shouldn't Be.
Here's the scenario. The column in question is called 'datein' and it's type is 'datetime'. I have three rows with the value of '2009-10-01 00:00:00.000' for 'datein'. Why does this query return the aforementioned rows? SELECT * FROM t_call AS tc WHERE tc.datein >= '2009-09-30 00:00:00.000' AND tc.datein <= '2009-09-30 ...
SQL - What type of join is it and the best way to rewrite?
Our 3rd Party app has a custom View with the following: FROM dbo.vwPositionAssetSubWeight INNER JOIN dbo.vwPositionAssetSubTotal ON dbo.vwPositionAssetSubWeight.AssetID = dbo.vwPositionAssetSubTotal.AssetID FULL OUTER JOIN dbo.vwPositionAssetPendingTrades ON dbo.vwPositionAssetSubWeight.AssetID = dbo.vwPositionAssetPendingTr...
How to make total?
Using SQLSERVER 2000 How to make a total of intime value Table 1 InTime 02:00:48 22:00:22 ....., Intime Datatype is varchar 02:00:12 - (HH:MM:SS) Before I tried in access 2003 select format( Int(24*sum(Intime)), '0') & format( sum(Intime) , ':ss' ) AS totaltime from table1 Above Query is working perfectly in Access 2003 How to...
Grabbing values from multiple xml rows in sql (ms server 2005)
Hi Guys, I will attempt to explain this situation as clearly as I can. I have this query below which tries to get a list of author_ids from an xml column in sql server 2005 associated with a book, but assigning the query to the @authorIds returns an error because it returns multiple rows, any ideas on how to run around this problem? Ma...
SQL query slow from .NET code, but not interactively
We are using an ORM that is executing a call from .NET to SQL Server's sp_executesql stored procedure. When the stored proc is called from .NET, we receive a timeout exception. Looking at Profiler, I can see that the query is indeed taking a long time to execute. The query is essentially: exec sp_executesql N'SELECT DISTINCT FROM [Ou...
sql server 4 byte unsigned int
Does anyone know of any work around by which i can save unsigned integers (0 to 4294967295) simply using 4 bytes instead of using 8 bytes and bigint? I know we can create user defined datatypes and create a constraint on them to not allow negative values but that still does not allow me to enter values over 2147483647. I only want to u...
convert varchar into datetime in sql server
How do i convert a string of format -> mmddyyyy into datetime in sql server 2008? My target column in 'DateTime' I have tried with Convert and most of the Date style values - I get a 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.' error message ...
Need Latest Event Of Certain Type Where Several Events May Exist For That Date
First of all, I'm working in SQL Server 2000 (although prayers have been made to the Federal Stimulus Money gods for an upgrade to 2008.) I have a listing of students and educational events. I can successfully get the most recent event, except that it may be a combination of events: StudentID Event Date 1 Triennia...
T/F: Using IF statements in a procedure produces multiple plans
In responses to this question, KM said if you are on or above SQL Server 2005, you can use IFs to have multiple queries in the same procedure and each will have an query plan saved for it (equiv to a procedure for each on older versions), see the article in my answer or this link to proper section: sommarskog.se/dyn-search-2005.html#...
How do I update a Table from another Table with exactly the same Format on SQL Server 2008
I have a backup like this: Select * Into BACKUP From ORIGINAL Then I need to restore some data from that backup, how do I update from that table, I can't Insert Into nor Drop Table because of the Foreign Keys ...
Will SQL Server NOLOCK hint return partially written rows?
First of all I'm not asking if NOLOCK should or should not be used. Let's get past that. I guess the question comes down to how sql server writes data? Is an entire row written at once or does it write it a column at a time? I'm asking because the NOLOCK hint is being considered. A dirty read is fine as long as the entire row is retu...
How to set the base time for DateTime in IIS (ASP) for compatibility mode?
We have updated our servers with Windows 2008 Server, but we are still using SQL Server 2005 in our production software. The problem comes in one part of the system where we store times as datetimes. Since ever, storing a time in a datetime column stored the base date as the date. It is 1900-01-01 in SQL Server 2005. Back then, IIS under...
SQL Server aggregates for very large tables
We have a table with 17Mil rows containing product attributes, let's say they're: brandID, sizeID, colorID, price, shapeID And we need to query for aggregates by brand and size. Currently we query and filter this data by doing something like this: select brandID, sizeID, count(*) from table where colorID in (1,2,3) and price=10...
Sql Query Optimization
I am not so proficient in TSql as of now (writing since last 4/5 months) but I have written many queries. Although I have given the outputs, sometimes I feel that the queries are not so optimized. I searched in google and found lot of stuffs about query optimization, and they ask to look into the query plan(actual & estimated) for the ...
Picking info using junction table(SQL SERVER 2005) [ SET BASED]
I have 3 tables 1) tblPurchaser having 2 columns: PurchaserId PurchaserName 1 A1 2 A2 3 A3 2) tblCar having 2 columns: CarId Carname 11 C1 12 C2 13 C3 14 C4 And the last is a junction table tblInformation where the information about those persons are given who has purchas...
Using LEFT on a TEXT-field in SQL Server
In a table, I have a text-field. I need to be able to select only the first 200 chars of the field - but LEFT does not work with TEXT-fields. What to do? ...
SQL Server TSQL conventions/patterns on SET, WITH and ENABLE
After years of using TSQL, I still cannot figure out when to use SET, WITH or ENABLE. When you read TSQL statement like, ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); It looks more intuitive and readable if it was written like (invalid query below), ALTER TABLE Person.Person SET CHANGE_TRACKI...
SQL UPDATE - IS there a more elegant and efficient way of doing this?
I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth). The idea is that the price records are created when a site is created as place holders. When a user clicks a Price row in a view - I need to update the prices for the res...
ID should not display between the specified date?
Using SQL Server 2000 Database1.Table1 ID Name Date title 001 Ravi 23-02-2009 Accountant 001 Ravi 24-02-2009 Accountant 001 Ravi 25-02-2009 Accountant 002 Ram 21-02-2009 Supervisors 002 Ram 25-02-2009 Supervisors 002 Ram 26-02-2009 Supervisors So on…, Database2.Table2 ID Name Date1 Date2 001 Ravi 23-02-2009 ...