Update(column) merely states that column participated in update, but not that its value has changed. For instance, update Person SET Name = Name yields true in update(name) even though Name has not been changed in any row. To check if new values differ from old ones, you would employ except because except will remove rows … Read more
Decimal and money ought to be pretty reliable. What i can assure you (from painful personal experience from inherited applications) is DO NOT use float!
Over the years a pile of developer sweat has gone into efficiently paging result sets. Yet, there is no one answer–it depends on your use case. Part of the use case is getting your page efficiently, part is figuring out how many rows are in a complete result set. So sorry if i stray a … Read more
On SQL Server 2005 and up, you can use something like this: ;WITH TableRows AS ( SELECT SUM(row_count) AS [RowCount], OBJECT_NAME(OBJECT_ID) AS TableName FROM sys.dm_db_partition_stats WHERE index_id = 0 OR index_id = 1 GROUP BY OBJECT_ID ) SELECT * FROM TableRows WHERE [RowCount] = 0 The inner select in the CTE (Common Table Expression) calculates … Read more
It’s incredibly clunky, but you can use a CASE statement for ordering: SELECT * FROM Blah ORDER BY CASE MyColumn WHEN ‘orange’ THEN 1 WHEN ‘apple’ THEN 2 WHEN ‘strawberry’ THEN 3 END Alternately, you can create a secondary table which contains the sort field and a sort order. TargetValue SortOrder orange 1 apple 2 … Read more
MS SQL does not have a trim function. You’ll need to use rTrim and lTrim together. update MyTable set Name = lTrim(rTrim(name))
Semicolon at the end of the statement. INSERT INTO myTable (ID) values (5);
This should do it: UPDATE TheTable SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + ‘-‘ + SUBSTRING(PhoneNumber, 4, 3) + ‘-‘ + SUBSTRING(PhoneNumber, 7, 4) Incorporated Kane’s suggestion, you can compute the phone number’s formatting at runtime. One possible approach would be to use scalar functions for this purpose (works in SQL Server): CREATE FUNCTION FormatPhoneNumber(@phoneNumber … Read more
OK, from an application developer’s point of view, here’s what these settings do: QUOTED_IDENTIFIER This setting controls how quotation marks “..” are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([…]) and can be used to quote SQL object names like table names, column names, etc. When it … Read more
No, there is no way — create/drop is the only choice.