There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we have been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature introduced in SQL Server 2012 and experience the difference.
SQL Server introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.
Here’s the syntax on how you can create a SEQUENCE using various options.
Syntax of SEQUENCE:
CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH ] [ INCREMENT BY ] [ { MINVALUE [ ] } | { NO MINVALUE } ] [ { MAXVALUE [ ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ ] } | { NO CACHE } ] [ ; ]
Lets now take a look at an example on how to use SEQUENCE in SQL Server.
Creating a sequence
Create a sample Item table
Use Sequence while inserting data
Query the table
Reset Sequence values
Alter or Delete Sequence values
Step 1: In this step, we will create a new object called ItemIDSequence that will start with a value of 1 and increment its value by 1.
CREATE SEQUENCE ItemIDSequence
START WITH 1
INCREMENT BY 1;
Step 2: In this step, we will create a sample ITEM table with just two columns.
Step 3: In this step, we will insert data in the ITEM table using ItemIDSequence which will automatically insert and increment the values of ItemIDSequence by 1.
INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'MANGO');
INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'APPLE');
INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'BANANA');
INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'ORANGE');
INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'GRAPES');
Step 4: In this step, we will query the ITEM table to review the data in the ITEM table. Notice that the ITEMNO column in the ITEM table has sequential numbers that were auto-inserted using ItemIDSequence.
SELECT * FROM ITEM
--You will see the following result set.
ITEMNO ITEMNAME
====================
1 MANGO
2 APPLE
3 BANANA
4 ORANGE
5 GRAPES
You can Alter, Delete or Query any sequence using the below examples:
--In this step, we will look at Altering, Deleting & Querying Sequences:
ALTER, DELETE, AND QUERY SEQUENCES
--To delete a sequence:
DROP SEQUENCE ItemIDSequence
--To alter a sequence:
ALTER SEQUENCE ItemIDSequence
RESTART WITH 900
INCREMENT BY 1
GO
--To query the next value of Sequence for a particular SEQUENCE object:
SELECT NEXT VALUE FOR ItemIDSequence
--To query all sequences available in a database, you can use the below query:
SELECT * FROM SYS.SEQUENCES
Important and Best Practices for Sequence:
Sequence values are not automatically protected after insertion into a table. You should use Update Trigger on a table to prevent sequence values from being changed.
Sequence does not automatically enforce uniqueness for sequence values. You should create unique index on the sequence column to enforce uniqueness.
If you have created a sequence (Example: 1 through 100) and if the rows in the table grows beyond 100 rows, SQL Server would start assigning values 1 through 100 again.
By default, if you do not specify data type for a sequence, BIGINT data type is used. (Note: you can create sequence of any integer data type)
Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations.
Some of the reasons include:
Load on SQL Server
Out of date statistics
Server configuration setting for degree of parallelism (MAXDOP)
Amount of available memory
Amount of available resources, etc.
SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process load. This means it could either grant or limit the number of CPUs Index operations can use. In most of the cases this is the best practice and you should not change the default value.
However, SQL Server can also use the index hint called MAXDOP. With MAXDOP indexing hint, you can now control the number of processors / CPU’s that can be used for performing index operations. Using MAXDOP indexing option enables parallelism for Index operations, which means it can use multiple processors to fulfill a single query statement which can potentially improve the performance of index operations such as:
Creating an index
Altering an index
Rebuilding an index
Dropping a clustered index, etc.
When you use the MAXDOP hint for any index operations, it will override the server level Max Degree of Parallelism value for that specific query. (All other Index operations with no MAXDOP will continue to honor the server level max degree of parallelism settings)
MAXDOP hint supports three values when used with any indexing operations:
MAXDOP = 0 This is the default server level setting. This instructs SQL Server to grant CPU’s based on the available resources and server load.
MAXDOP = 1 This value disables use of parallelism and enables the operations to execute serially.
MAXDOP = 2through 64 This value indicates SQL Server to use the number of CPU’s specified in the value to process index operations.
Keep in mind that if your SQL Server has only 8 CPU’s and you specify more CPU’s (Ex: MAXDOP = 32) then SQL Server will default to the number of CPU’s available on the system.
Here’s an example of how you can write an Index creation statement that would use 6 CPUs out of 8 when creating an index:
Create NonClustered Index EmailAddress_IDX on
Employee.Contact(EmailAddress)
With (MAXDOP=6);
Note: Please make sure to test this option in the test environment before implementing it in the production environment.
SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.
Let’s assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH its much easier.
OFFSET and FETCH when used together can return the exact set of data you are looking for.
OFFSET: Allows you to offset the record pointer to a specific row in a table
FETCH: Allows you to fetch/return number of rows you request in Fetch.
Lets take an example of querying rows from a table that has 50,000 records. Lets query 1200 records starting from 25,000 thousand record.
Step 1 – Create a table
CREATE TABLE ITEMS
(
ITEM_ID INT IDENTITY(1,1),
ITEM_NAME VARCHAR(50)
);
--IN THIS QUERY, WE ARE OFFSETTING/SKIPPING THE ROWS BY 3 RECORDS AND RETURNING THE NEXT 5 ROWS.
SELECT * FROM ITEMS
ORDER BY ITEM_ID
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY
GO
--EXECUTING THE ABOVE STATEMENT WILL RETURN THE BELOW RESULT SET AS REQUESTED:
ITEM_ID ITEM_NAME
4 KIWI
5 PLUMS
6 GRAPES
7 WATERMELON
8 HONEYDEW
(5 row(s) affected)
--NOTE: YOU CAN ALSO USE VARIABLES WITH OFFSET AND FETCH CLAUSES.
DECLARE @OFFSET INT=3, @FETCH INT=5
SELECT * FROM ITEMS ORDER BY ITEM_ID
OFFSET @OFFSET ROWS
FETCH NEXT @FETCH ROWS ONLY
GO
--EXECUTING THE ABOVE COMMAND WILL RETURN THE SAME RESULTS AS THE USING CONSTANT VALUE WITH OFFSET AND FETCH.
ITEM_ID ITEM_NAME
4 KIWI
5 PLUMS
6 GRAPES
7 WATERMELON
8 HONEYDEW
(5 row(s) affected)
CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server. This eliminates the need of explicit data conversions when concatenating two values.
Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.
Let’s look at an example using Concat string function: In this example, we will use three strings stored in different variables and then concatenate the strings using Concat function.
Note: Concat function can concatenate up to 254 values.
Test 1 – Concatenating with CONCAT function
Declare @a Varchar(100)='Microsoft SQL Server is now '
Declare @b int=32
Declare @c varchar(200)=' years old'
Select CONCAT (@a, @b, @c)
Go
--Above script returns the following value:
Microsoft SQL Server is now 32 Years old
Test 2 – Let’s take similar example but with data type conversion:
Declare @a Varchar(100)='Microsoft SQL Server is now '
Declare @b int=32
Declare @c varchar(200)=' years old'
Select @a+Convert(varchar,(@b))+@c
--Please note the convert function usage above
Returns:
Microsoft SQL Server is now 32 Years old
In the above examples, observe the use of CONCAT() function. There’s no data conversion being performed in the 1st test. However, in the 2nd, we are using data conversion function to convert Integer value to a string.
The data returned by CONCAT function depends on the type of values passed. The below table shows the mapping of input and output types:
Input Value / Type
Output Value / Type
SQL-CLR (TYPES & UDT)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(<=4000)
NVARCHAR(<=4000)
VARBINARY(MAX)
NVARCHAR(MAX)
ALL OTHER INPUT TYPES
VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)
NOTE: CONCAT function only works with all the versions above SQL12. However, to make this work in older versions of SQL Server, you will need to create your own custom function that can take care of concatenating values and appropriately converting their data types.
If you are running your databases on a Windows network environment, the best practice is to use Windows Authentication rather than Mixed Authentication (Both Windows and SQL Authentication). However, there are many companies that still use Mixed Authentication which allows both AD accounts and SQL Accounts. If your organization MUST use SQL authenticated accounts (SQL Logins), you must ensure that your SA account is locked down as completely as possible and that only key people in the team know the password.
The most obvious first steps is to utilize a complex passphrase, create separate accounts for each DBA, and then ensure that nothing ever uses the SA account.
However, there is still an issue to resolve with someone attempting to hack into your SQL Server by using a brute force attack against the SA account. Just like the Administrator account in Windows, the SA account can NOT be locked out, regardless of the number of failed login attempts.
Being a smart DBA or a System Admin, you can defeat the brute force attacks on the Administrator (SA) account in a very simple way and that is by simply renaming the Administrator account of your SQL Servers. Once you change the SA account, hackers will fail when using SA account (default SA account) when trying to connect using SA account since the account is now renamed or is invalid.
Let’s take a look at the steps required to rename the default SA account: Note: Renaming an SA account was not possible prior to SQL Server 2005.
Renaming SA Account
ALTER LOGIN SA WITH NAME = [SALEEM]
GO
Once the above step is performed (i.e. SA account has been renamed), let’s disable the SA account:
Securing SQL Server is one of the top priorities of any DBA. It is important for DBA’s to make sure that the database server is TIGHTLY SECURED and is not exposed to users that do not need access to SQL Server. Only valid and approved users MUST be granted required permissions.
Apart from configuring the right security/permissions for SQL Server users/logins, it is also important to select the right account to run SQL Server services. In todays tip, we will explore when to use what service accounts for running SQL Server services.
When to use a Domain User Account for running SQL Server Service?
If your SQL Server interacts with other servers, services or resources on the network (ex: Files Shares, etc.) or if your SQL Server services uses linked servers to connect to other SQL Servers on the network, then you may use a low privileged domain user account for running SQL Server services. Domain user account is the most recommended account for setting up SQL Server services that interact with other servers on the network. One of the plus points of using a Domain User Account is that the account is controlled by Windows active directory therefore, domain level policy on accounts apply to SQL Server service account as well.
When to use a Network Service Account for running SQL Server Service?
NEVER should you use Network Service Account for running SQL Server services. Network Service accounts are shared with other services running on the local computer. Network Service Account is a built-in account that has more access to server resources and objects than users accounts of local user groups.
Any SQL Server services that runs on Network Service Account, can access network resources by using the credentials of the computer account. This account shows up as NET AUTHORITY\NETWORK SERVICE when configuring SQL Server Services.
When to use Local System Account for running SQL Server Service?
NEVER should you use local system account for running SQL Server services. Local System Account has more permissions than you would think. It is a very high-privileged built-in account created by Windows O/S.
Local System Account has extensive privileges on the entire local system and acts as a computer on your company’s network. This account shows up as NT AUTHORITY\SYSTEM when configuring SQL Server services.
When to use Local User Account for running SQL Server Service?
If your SQL Server DOES NOT interact with other servers, services or resources on the network (ex: Files, Shares, Linked Servers, Etc.) then you may use a low privileged local user account for running SQL Server Services.
NOTE: You are not required to use the local user account with administrative privileges to run SQL Server services.
Recent Comments