you didn’t specify your DBMS, so the following is ANSI SQL: select prev_word, word, next_word from ( select id, lag(word) over (order by id) as prev_word, word, lead(word) over (order by id) as next_word from words ) as t where word = ‘name’; SQLFiddle: http://sqlfiddle.com/#!12/7639e/1
This works for me to find queries on any database in the instance. I’m sysadmin on the instance (check your privileges): SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID(‘msdb’) ORDER BY deqs.last_execution_time DESC This is the same answer that Aaron Bertrand provided … Read more
I encountered some performance issues using the COUNT() OVER() method. (I’m not sure if it was the server as it took 40 seconds to return 10 records and then later didn’t have any issues.) This technique worked under all conditions without having to use COUNT() OVER() and accomplishes the same thing: DECLARE @PageSize INT = … Read more
Try this solution based on T-SQL and XQuery((root/row)[position() <= sql:variable(“@count”)]): T-SQL Scalar function: CREATE FUNCTION dbo.SUBSTRING_INDEX ( @str NVARCHAR(4000), @delim NVARCHAR(1), @count INT ) RETURNS NVARCHAR(4000) WITH SCHEMABINDING BEGIN DECLARE @XmlSourceString XML; SET @XmlSourceString = (SELECT N'<root><row>’ + REPLACE( (SELECT @str AS ‘*’ FOR XML PATH(”)) , @delim, N'</row><row>’ ) + N'</row></root>’); RETURN STUFF ( … Read more
SQL Server is following the ANSI/ISO standard for string comparison. The article How SQL Server Compares Strings with Trailing Spaces explains this in detail. SQL Server follows the ANSI/ISO SQL-92 specification… on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match … Read more
It turned out to be easy enough: create table mytable ( id bigint not null constraint DF_mytblid default next value for mainseq, code varchar(20) not null ) or if the table is already created: alter table mytable add constraint DF_mytblid default next value for mainseq for id (thank you Matt Strom for the correction!)
Try GRANT ADMINISTER BULK OPERATIONS TO [server_login]. It is a server level permission, not a database level. This has fixed a similar issue for me in that past (using OPENROWSET I believe).
I came across this: https://entityframework.codeplex.com/wikipage?title=Interception And it appears that you can do something like this: public class HintInterceptor : DbCommandInterceptor { public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext) { command.CommandText += ” option (recompile)”; base.ReaderExecuting(command, interceptionContext); } } And register it like this (I did it in Application_Start of global.asax.cs): DbInterception.Add(new HintInterceptor()); And it will … Read more
One approach is the difference of row numbers: select name, count(*) from (select t.*, (row_number() over (order by id) – row_number() over (partition by name order by id) ) as grp from t ) t group by grp, name; The logic is easiest to understand if you run the subquery and look at the values … Read more
I think you will find your answer here Using the identity attribute for a column, you can easily generate auto-incrementing numbers (which as often used as a primary key). With Sequence, it will be a different object which you can attach to a table column while inserting. Unlike identity, the next number for the column … Read more