Script to Drop All Orphaned SQL Server Database Users

Today my latest tip has been published about “Script to Drop All Orphaned SQL Server Database Users” and you can read it at http://www.mssqltips.com/sqlservertip/3439/script-to-drop-all-orphaned-sql-server-database-users/
Please let me know if you have any comments or questions. Thanks for reading!

Microsoft SQL Server 2014 In-Memory OLTP: How fast is it?

My latest tip was published today at MSSQLTips.com, you can read it at http://www.mssqltips.com/sqlservertip/3392/microsoft-sql-server-2014-inmemory-oltp-how-fast-is-it/ . Thanks for reading again!

How to use DBCC CHECKPRIMARYFILE to attach several SQL Server databases in seconds

A few hours ago my tip has been published at mssqltips.com about “How to use DBCC CHECKPRIMARYFILE to attach several SQL Server databases in seconds”. You can read it at http://www.mssqltips.com/sqlservertip/3342/how-to-use-dbcc-checkprimaryfile-to-attach-several-sql-server-databases-in-seconds/ . Thanks for reading!

Error 601: Could not continue scan with NOLOCK due to SQL Server data movement

Just one hour ago one of my tips was published at MSSQLTips.com related to “Error 601: Could not continue scan with NOLOCK due to SQL Server data movement”. You can read it at http://www.mssqltips.com/sqlservertip/3289/error-601-could-not-continue-scan-with-nolock-due-to-sql-server-data-movement/ . Thanks for reading!

SQL Server Replication Error – The specified LSN for repldone log scan occurs before the current start of replication in the log

My latest tip has been published today at mssqltips.com about “SQL Server Replication Error – The specified LSN for repldone log scan occurs before the current start of replication in the log” and you can read it at http://www.mssqltips.com/sqlservertip/3288/sql-server-replication-error–the-specified-lsn-for-repldone-log-scan-occurs-before-the-current-start-of-replication-in-the-log . Thanks for reading!

SQL Server Transactional Replication Error: Could not find stored procedure error and how to recover it by using sp_scriptpublicationcustomprocs

Today my tip about how to fix the “SQL Server Transactional Replication Error: Could not find stored procedure” has been published online in mssqltips.com, you can read it at http://www.mssqltips.com/sqlservertip/3287/sql-server-transactional-replication-error-could-not-find-stored-procedure-error-and-how-to-recover-it-by-using-spscriptpublicationcustomprocs/ . Thanks for reading!

Error: Index was outside the bounds of the array

It is said that we may work on SQL Server connecting to Object Explorer by using the SQL Management Studio tool (SSMS) of an older version. It is true up to a point, but there is a risk of getting some unexpected errors, for instance, we might get this error while working connected to SQL Server 2012 Object Explorer by using SQL Server 2005 SSMS.



More specifically, the error is raised because superior versions support more features, which means that each feature is likely to have one folder on the Object Explorer. Put differently, there is a bigger number of folders to display in SQL Server 2012 than SQL Server 2005 whose Object Explorer cannot display more because the max array index is outside the bounds that this supports. Ideally, it is highly advisable to always use the latest SSMS to connect to older versions (but not vice versa). As simple as water. That is all for now, let me know any remarks you may have. Thanks for reading.

SQL11 (Denali): New object SEQUENCE

SQL Server 2012 CTP1 is coming up with a new object named SEQUENCE which allows to manage sequence numbers between 2^31 – 1 and 2^31 –1. SEQUENCE solves many problems with respect to using the IDENTITY property because it is not tied to the column and can be used for many columns in different tables. In this capacity, this novelty is useful as alternative solution and in replacement of ROW_NUMBER for some cases. SEQUENCE can be used with UNION ALL but not with Functions, DISTINCT, UNION, EXCEPT and INTERSECT. The way of using is very easy, practical, flexible and  offers better performance than IDENTITY. Creating the right indexes on columns which takes values from SEQUENCE object will help tremendously, indeed.

Creating a SEQUENCE object is simple.

CREATE SEQUENCE dbo.MySeq
    AS INT 
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 100
    CYCLE 
    CACHE 20
;

Now using it:

SELECT NEXT VALUE FOR dbo.MySeq
GO 4

The result would be something like this:

(no column name)

1

2

3

4

If we execute again the previous query, the result will be numbers from 5 to 8, and so on. However, if we want to reinitialise the sequence, it can be done as follows:

ALTER SEQUENCE dbo.MySeq RESTART 

Now using SEQUENCE as an alternative of ROW_NUMBER:

select next value for dbo.MySeq as [nro],  Employees.FirstName   from Employees 

And also in this way:

select next value for dbo.MySeq  over (order by customers.CustomerID ) as [nro], 
       customers.CustomerID,  Customers.ContactName 
 from Customers 

Here are some resources to check out more information about SEQUENCE:

• Creating and Using Sequence Numbers.
  http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
• sp_sequence_get_range (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ff878352(v=SQL.110).aspx
• SQL Server v.Next (Denali): Using SEQUENCE http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx

That is all for now, let me know any remark you may have. Thanks for reading.