SQL update top1 row query
With cte as ( select top(1) shipfirtsname From ShipBillInfo where CustomerId=’134′ order by OredrGUID desc) Update cte set shipfirstname=”abc”;
sql-server-2008
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range?
DateTime has the range: January 1, 1753, through December 31, 9999 DateTime2 has the range: 0001-01-01 through 9999-12-31 So if you are entering a date before 1753 you would get this error when the field in the table is of type DateTime.
Average of multiple columns
You don’t mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008) SELECT *, (SELECT AVG(c) FROM (VALUES(R1), (R2), (R3), (R4), (R5)) T (c)) AS [Average] FROM Request The 2005 version is a bit more tedious SELECT *, (SELECT AVG(c) FROM … Read more
When is it better to write “ad hoc sql” vs stored procedures [duplicate]
SQL Server caches the execution plans for ad-hoc queries, so (discounting the time taken by the first call) the two approaches will be identical in terms of speed. In general, the use of stored procedures means taking a portion of the code needed by your application (the T-SQL queries) and putting it in a place … Read more
no sqljdbc_auth in java.library.path
1) Download the JDBC Driver here. 2) unzip the file and go to sqljdbc_version\fra\auth\x86 or \x64 3) copy the sqljdbc_auth.dll to C:\Program Files\Java\jre_Version\bin 4) Finally restart eclipse
SQL query for getting data for last 3 months
SELECT * FROM TABLE_NAME WHERE Date_Column >= DATEADD(MONTH, -3, GETDATE()) Mureinik’s suggested method will return the same results, but doing it this way your query can benefit from any indexes on Date_Column. or you can check against last 90 days. SELECT * FROM TABLE_NAME WHERE Date_Column >= DATEADD(DAY, -90, GETDATE())
How to do a case sensitive GROUP BY?
You need to cast the text as binary (or use a case-sensitive collation). With temp as ( select ‘Test’ as name UNION ALL select ‘TEST’ UNION ALL select ‘test’ UNION ALL select ‘tester’ UNION ALL select ‘tester’ ) Select Name, COUNT(name) From temp Group By Name, Cast(name As varbinary(100)) Using a collation: Select Name Collate … Read more
Restrict SQL Server Login access to only one database
I think this is what we like to do very much. –Step 1: (create a new user) create LOGIN hello WITH PASSWORD=’foo’, CHECK_POLICY = OFF; — Step 2:(deny view to any database) USE master; GO DENY VIEW ANY DATABASE TO hello; — step 3 (then authorized the user for that specific database , you have … Read more
When using GETDATE() in many places, is it better to use a variable?
[NOTE: If you are going to downvote this answer, please leave a comment explaining why. It has already been downvoted many times, and finally ypercube (thank you) explained at least one reason why. I can’t remove the answer because it is accepted, so you might as well help to improve it.] According to this exchange … Read more
SQL Server SELECT where any column contains ‘x’
You can use in: SELECT * FROM testing WHERE ‘foo’ in (col1, col2, col3, . . . );