If I want to select all records in a table that have not been processed yet and then update those records to reflect that they have been processed, I would do the following: SELECT * FROM [dbo].[MyTable] WHERE [flag] IS NULL; UPDATE [dbo].[MyTable] SET [flag] = 1 WHERE [flag] IS NULL; How do I ensure that the UPDATE works on only th...
I am trying to determine a SCORE from 11 rows in a table. Those 11 rows are being aggregated into five rows using a ScoringCategoryID column as follows... ScoringCategoryID CategoryScore PercentOfTotal --------------------------------------------------------- 7 15.00 0.40 8 15.0...
Here is my query (from a trigger): UPDATE QuoteItemsGroupFeature SET Cost = (QuoteItemsGroup.BaseCost + QuoteItemsGroup.AccumulatedCost + ISNULL(SUM(ParentQuoteItemsGroupFeature.Cost), 0)) * INSERTED.Amount FROM QuoteItemsGroupFeature INNER JOIN INSERTED ON QuoteItemsGroupFeature.QuoteItemsGroupFeatureId = INSERTED.QuoteIt...
CREATE TABLE exmp_test ( id int, v1 int, v2 int, v3 int, v4 int ) SELECT * FROM exmp_test id v1 v2 v3 v4 1 2 4 6 7 1 4 77 3 8 I want to add the value of the [id] column to (whichever has least value for v1 ,v2 ,v3 ,v4) for each row. As an example, for the first row, the [id] value should be add to v1 (because it ...
I try to convert a string into a date in t-sql. However get results that I can't explain. DECLARE @String as char(11) DECLARE @TString as char(11) SELECT @String = SUBSTRING([Flat File Source Error Output Column],1,CHARINDEX(',',[Flat File Source Error Output Column])-6) FROM [ERROR].[Import_V2X] SELECT @TString = '12/18/2009' -- Che...
I know how to use indexes(clustured and non clustured) But when should i use non clustured indexes in my table. What scenarios should be there, so as to make my column non clustured index. I have gone throught msdn guidelines but still little bit confusion. Should i make only unique columns as NC or should there any other columns also a...
I run this query "insert into students (StudentName) values ('reza');insert into Records (RecordValue,StudentID)" + " values (20,@@IDENTITY)"; in C# and get following exception : Characters found after end of SQL statement ...
I have a SQL Server table that contains postal addresses. In preparation for mailing, I need to do a number of string replacements to conform with USPS preferences ("Avenue" becomes "Ave", for example). To save me the trouble of enumerating all of the replacements, I have the label/abbreviation pairs saved in a two-column table. Is ther...
I understand how to use a case statement to return different values: SELECT CASE Color WHEN 'Blue' THEN 'Water' WHEN 'Black' THEN 'Oil' WHEN 'Red' THEN 'Blood' END FROM dbo.Liquid Is there a way to use it to control flow instead of IF-ELSE, i.e. DECLARE @Color varchar() SELECT @Color = Color FROM d...
Hello guys. I have two tables : create table CurrentDay ( ID int identity primary key, ssn varchar(10), val money, CheckDate datetime, CurrentStatus tinyint) create table PreviousDay ( ID int identity primary key, ssn varchar(10), val money, CheckDate datetime, CurrentStatus tinyint) I need update field CurrentDay.CurrentStatus...
I'm not sure how to explain this probably, so I'll use an example. I have to store (etc.) some users ID in a single value/column in my SQL Server table, so one value could look like this "4231 3271 3722 7432 4831 3192 ". of course this can be stored as a text, char, nchar, nvarchar and som on. But which is the best? if I have to only st...
I have created script to find selectivity of each columns for every tables. In those some tables with less than 100 rows but selectivity of column is more than 50%. where Selectivity = Distinct Values / Total Number Rows So, are those column are eligible for index? Or, can you tell, how much minimum rows require for eligibility to create...
I am looking for a way to insert code comment blocks semi-automatically in all the views, stored procedures, and UDFs in a database (basically every code-based module). I'm fine with generating the ALTER statements and reviewing them myself before running the scripts, I was just looking for a reliable way to generate them. I am not loo...
How do I write an IF statement with multiple arguments in T-SQL? Current source error: DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = NULL SET @EndDate = NULL IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) BEGIN -- do some work END It throws the following error: Incorrect syn...
I've been looking for a good equivalent to the Oracle LEAST function. I'm hoping to implement a user defined function that does about 10 fairly complex calculations, and takes the minimum value from each of those calculations. What I would do in Oracle is: SELECT LEAST ( select expression1 from dual, select expression2 from dual, sel...
Is there a function or a workaround in SQL Server 2005 that is equivalent to the new_time() function in oracle? ...
Is there a way how we can know when was a database taken offline? Platform: SQL server 2005 ...
I need to drop a perticular database in SQL Server 2005 using T-SQL. I know droping a databse can not be done in a database trnsaction. So what if while deleting a real huge database some unexpected exception occurs E.g. Time out exception. Will it leave my database in unstable state ? If yes, how do i avoid such situation ? ...
Having a problem with a WHERE search statement would like to use a construction like.. WHERE f.foo IN CASE @bar WHEN 'BAR' THEN ('FOO','BAR',BAZ') WHEN 'BAZ' THEN ('FOOBAR','FOOBAZ') END or WHERE CASE @bar WHEN 'BAR' THEN f.foo IN ('FOO','BAR',BAZ') WHEN 'BAZ' THEN ...