To round to the nearest whole day, there are three approaches in wide use. The first one uses datediff to find the number of days since the 0 datetime. The 0 datetime corresponds to the 1st of January, 1900. By adding the day difference to the start date, you’ve rounded to a whole day; select … Read more
This should do it: WITH MyTest as ( SELECT P.ProductID, P.ParentID, CAST(P.ProductID AS VarChar(Max)) as Level FROM Products P WHERE P.ParentID = 0 UNION ALL SELECT P1.ProductID, P1.ParentID, CAST(P1.ProductID AS VarChar(Max)) + ‘, ‘ + M.Level FROM Products P1 INNER JOIN MyTest M ON M.ProductID = P1.ParentID ) SELECT * From MyTest And here’s the … Read more
No. Not a documented 100% supported way. There is an approach sometimes used for calculating running totals called “quirky update” that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee. But what version … Read more
Is your SQL Server in ‘mixed mode authentication’ ? This is necessary to login with a SQL server account instead of a Windows login. You can verify this by checking the properties of the server and then SECURITY, it should be in ‘SQL Server and Windows Authentication Mode’ This problem occurs if the user tries … Read more
You can export the data to a flat file which will not be truncated. To do this: Right click the Database Click Tasks -> Export Data Select your Data Source (defaults should be fine) Choose “Flat File Destination” for the Destination type. Pick a file name for the output. On the “Specify Table Copy or … Read more
INSERT INTO <table> SELECT <natural keys>, <other stuff…> FROM <table> WHERE NOT EXISTS — race condition risk here? ( SELECT 1 FROM <table> WHERE <natural keys> ) UPDATE … WHERE <natural keys> there is a race condition in the first INSERT. The key may not exists during the inner query SELECT, but does exist at … Read more
Straightforward way? No, but I’ve used the reverse. Literally. In prior routines, to find the last occurence of a given string, I used the REVERSE() function, followed CHARINDEX, followed again by REVERSE to restore the original order. For instance: SELECT mf.name ,mf.physical_name ,reverse(left(reverse(physical_name), charindex(‘\’, reverse(physical_name)) -1)) from sys.master_files mf shows how to extract the actual … Read more
Create two tables and then join to them to convert stored GMT dates to local time: TimeZones e.g. ——— —- TimeZoneId 19 Name Eastern (GMT -5) Offset -5 Create the daylight savings table and populate it with as much information as you can (local laws change all the time so there’s no way to predict … Read more
There’s a really good paper by Microsoft Research called To Blob or Not To Blob. Their conclusion after a large number of performance tests and analysis is this: if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient if your pictures or document are … Read more
SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options. One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data. DECLARE @S varchar(max) = ‘A’ SET @S … Read more