Access the “previous row” value in a SELECT statement
Use the lag function: SELECT value – lag(value) OVER (ORDER BY Id) FROM table Sequences used for Ids can skip values, so Id-1 does not always work.
sql-server-2008
Delete duplicate rows keeping the first row
I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
SQL Server Automated Backups [closed]
If you are using SQL Server Express, you won’t find a UI to run periodic backups. In this case you have to run a batch using Windows Scheduled Tasks or something similar. Don’t forget to use a user with enough privileges to access SQL Server. In the batch file “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE” -S (local)\SQLExpress … Read more
SQL problem with error “Invalid data type”
I know this is an old post but since I encountered the same issue and was able to solve it, thought of sharing it. This is an IntelliSense cache issue and could be solved by pressing ctrl+shift+R (shortcut for Edit -> IntelliSense -> Refresh Local Cache)
Storing query results into a variable and modifying it inside a Stored Procedure
Yup, this is possible of course. Here are several examples. — one way to do this DECLARE @Cnt int SELECT @Cnt = COUNT(SomeColumn) FROM TableName GROUP BY SomeColumn — another way to do the same thing DECLARE @StreetName nvarchar(100) SET @StreetName = (SELECT Street_Name from Streets where Street_ID = 123) — Assign values to several … Read more
How to check if change tracking is enabled
You can use this query: SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID(‘MyDatabase’)
What does it mean when Statement.executeUpdate() returns -1?
As the statement executed is not actually DML (eg UPDATE, INSERT or EXECUTE), but a piece of T-SQL which contains DML, I suspect it is not treated as an update-query. Section 13.1.2.3 of the JDBC 4.1 specification states something (rather hard to interpret btw): When the method execute returns true, the method getResultSet is called … Read more
Add emoji / emoticon to SQL Server table
Use NVARCHAR(size) datatype and prefix string literal with N: CREATE TABLE #tab(col NVARCHAR(100)); INSERT INTO #tab(col) VALUES (N’👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴 😭’); SELECT * FROM #tab; db<>fiddle demo Output: ╔═════════════════════════════════╗ ║ col ║ ╠═════════════════════════════════╣ ║ 👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴😭 … Read more
Easiest Way To Diff Two Table Schemas In SQL Server 2008?
I’m a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
Finding blocking/locking queries in MS SQL (mssql)
You may find this query useful: SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0 To get the query itself use this one: SELECT text,* FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0