Thursday 6 November 2014
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!
Categories:
DBA,
In-Memory OLTP,
Performance Tuning
Wednesday 24 September 2014
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!
Thursday 21 August 2014
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!
Saturday 16 August 2014
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!
Friday 25 July 2014
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!
Monday 23 December 2013
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.
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.
Sunday 12 December 2010
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.
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.
Friday 12 November 2010
SQL11 (Denali): OFFSET and FETCH (New options for ORDER BY)
It is known that ORDER BY clause has been improved in 'Denali' (code name of the upcoming version SQL Server 2012) adding more options as a result from looking for new solutions to work on specific business cases such as fetching top N rows per window of results from the result set which we used to do by combining TOP, OVER, and ORDER BY. Nevertheless, the performance of this approach was poor and not even close to being as good as OFFSET and FETCH in 'Denali'.
OFFSET keyword is used for excluding first N rows (default value is zero) whereas FETCH is used for reading/fetching M rows per each window of results starting from position (N+1). Therefore, OFFSET and FETCH allow to get only rows from position (N+1) to (N+M). I do recommend creating one Index (preferably Index Clustered) on ORDER BY columns and using these columns on WHERE clause as much as possible in order to avoid costly operations such as Index Scans or Table Scans. I have performed many tests on 10 millions of rows, and to be to honest, it worked very well. To illustrate, the following query will start fetching from position 4+1=5 (OFFSET position=4 for SQL Server) to the end of the table.
Now with DESC the results will be different (excluding last 4 rows)
Basically, each execution of the query is done independently, which means that not only are the results sent back to the client side, but also immediately the resources are released. Here is another example how to get only the top 3 rows excluding first 4 rows (OFFSET=4), which means that it only reads the fifth, sixth, and seventh rows.
Having a need to read the next three rows (eighth, ninth, and tenth rows), we have to increase OFFSET value in three.
Here the script to read dynamically each result by using variables (and optionally OPTIMIZE FOR hint to optimise this Ad-Hoc query).
Luckily, OFFSET and FETCH are supported by subqueries, functions, derived tables, but not by indexed views and using directly with TOP, OVER, INSERT, UPDATE, MERGE, o DELETE (except inside of independent queries at lower level such as subqueries). Here an illustration.
Finally, using it with Views.
To sum up, OFFSET and FETCH are excellent improvements which allow to implement solutions to fetch only specific pages/windows of results from the result set with ease. Let me know any remarks you may have. Thanks for reading!
OFFSET keyword is used for excluding first N rows (default value is zero) whereas FETCH is used for reading/fetching M rows per each window of results starting from position (N+1). Therefore, OFFSET and FETCH allow to get only rows from position (N+1) to (N+M). I do recommend creating one Index (preferably Index Clustered) on ORDER BY columns and using these columns on WHERE clause as much as possible in order to avoid costly operations such as Index Scans or Table Scans. I have performed many tests on 10 millions of rows, and to be to honest, it worked very well. To illustrate, the following query will start fetching from position 4+1=5 (OFFSET position=4 for SQL Server) to the end of the table.
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address FROM dbo.Customers ORDER BY CustomerID OFFSET (4) ROWS
Now with DESC the results will be different (excluding last 4 rows)
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address FROM dbo.Customers ORDER BY CustomerID DESC OFFSET (4) ROWS
Basically, each execution of the query is done independently, which means that not only are the results sent back to the client side, but also immediately the resources are released. Here is another example how to get only the top 3 rows excluding first 4 rows (OFFSET=4), which means that it only reads the fifth, sixth, and seventh rows.
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (4) ROWS FETCH NEXT 3 ROWS ONLY -- FETCH FIRST 3 ROWS ONLY GO
Having a need to read the next three rows (eighth, ninth, and tenth rows), we have to increase OFFSET value in three.
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (7) ROWS ---ó OFFSET (7) ROW FETCH NEXT 3 ROWS ONLY -- ó FETCH FIRST 3 ROWS ONLY GO
Here the script to read dynamically each result by using variables (and optionally OPTIMIZE FOR hint to optimise this Ad-Hoc query).
DECLARE @Start INT, @Next INT SET @Start=0 SET @Next=3 SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (@Start) ROWS FETCH NEXT @Next ROWS ONLY OPTION(OPTIMIZE FOR (@Start=4,@Next=5)) GO
Now the same code inside an stored procedure.
CREATE PROCEDURE dbo.PageFetch(@PageNumber INT, @PageSize INT) AS BEGIN SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS 'Nro', CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (@PageSize * (@PageNumber -1) ) ROWS FETCH NEXT @PageSize ROWS ONLY END
Querying the results for second, third and fifth windows of results with OFFSET.
EXEC dbo.PageFetch 2,3; GO EXEC dbo.PageFetch 3,3; GO EXEC dbo.PageFetch 5,3;
Luckily, OFFSET and FETCH are supported by subqueries, functions, derived tables, but not by indexed views and using directly with TOP, OVER, INSERT, UPDATE, MERGE, o DELETE (except inside of independent queries at lower level such as subqueries). Here an illustration.
SELECT TOP(2) ROW_NUMBER() OVER(ORDER BY CustomerID) AS 'NRO', CompanyName, ContactName, ContactTitle FROM ( SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (4) ROWS ---ó OFFSET (4) ROW FETCH NEXT 3 ROWS ONLY -- ó FETCH FIRST 3 ROWS ONLY ) AS T1
Finally, using it with Views.
CREATE VIEW dbo.v1 as SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../ FROM dbo.Customers ORDER BY CustomerID OFFSET (0) ROWS ---ó OFFSET (4) ROW GO SELECT CustomerID, CompanyName, ContactName FROM dbo.v1 WHERE CustomerID LIKE 'AN%'
To sum up, OFFSET and FETCH are excellent improvements which allow to implement solutions to fetch only specific pages/windows of results from the result set with ease. Let me know any remarks you may have. Thanks for reading!
Categories:
DBA,
Development,
Performance Tuning
HELLO, I'M PERCY REYES! I've been working as a senior SQL Server Database Engineer for over 20 years; I'm a three-time Microsoft Data Platform MVP. I'm a cryptographer conducting research on cryptographic Boolean functions and their applications.