SQL SERVER 2016

Learn more about SQL Server 2016 :

https://www.microsoft.com/en-us/cloud-platform/sql-server-features


Get Started with SQL Server 2016:

https://www.microsoft.com/en-us/cloud-platform/sql-server-resources

SQL Server 2012 New feature:Data Quality Services (DQS)

Refer the below link to learn about SQL Server 2012 new feature Data Quality Services (DQS)

http://technet.microsoft.com/en-us/sqlserver/hh780961.aspx

Introducing IIF() to SQL Server Family !!!

IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

 

In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.

 

 

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @A INT=40 DECLARE @B INT=30 SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A') GO;     Executing the above T-SQL will return the following result:   ------------------- A IS GREATER THAN B   (1 row(s) affected)

 

In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF.

DECLARE @JOHN_AGE INT=35 DECLARE @JULIE_AGE INT=29 SELECT IIF(@JOHN_AGE > @JULIE_AGE, 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS', 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS') GO;     Executing the above statement will return the follocwing result:   -------------------------------------------- JOHN IS OLDER THAN JULIE BY 6 YEARS   (1 row(s) affected)

 

Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels

 credits to Saleem Hakani.  my SQL Hero.

INTRODUCING SEQUENCE IN SQL SERVER 2012

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve 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 added in SQL Server 2012 and experience the difference.

SQL Server 2012 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.

CREATE SEQUENCE SYNTAX

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 } ]     [ ; ]

Let’s now take a look at an example on how to use SEQUENCE in SQL Server 2012.

In the below example, we will look at:

§  Creating a sequence

§  Create a sample Item table

§  Use SEQUENCE when inserting data in a table

§  Query Item table

§  Alter or delete SEQUENCE from SQL Server

In this step, we will create a new object called MYSEQUENCE1 that will start with a value of 1 and increment it’s value by 1.

STEP 1 - CREATING A SEQUENCE CALLED MYSEQUENCE1

CREATE SEQUENCE ITEMIDSEQUENCE       START WITH 1       INCREMENT BY 1;

In this step, we will create a sample ITEM table with just two columns.

STEP 2 - CREATING ITEM TABLE

CREATE TABLE ITEM ( ITEMNO INT, ITEMNAME VARCHAR(50) ); GO

In this step, we will insert data in the ITEM table by leveraging the newly create MYSEQUENCE1 which will automatically insert and increment the values of MYSEQUENCE1 by 1.

STEP 3 – USING SEQUENCE FOR INSERTING DATA IN A TABLE

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'); GO

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 sequence.

STEP 4 – QUERYING DATA FROM A TABLE USING SEQUENCE

SELECT * FROM ITEM YOU’LL SEEE THE FOLLOWING RESULT SET. ITEMNO ITEMNAME 1 MANGO 2 APPLE 3 BANANA 4 ORANGE 5 GRAPES

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

THINGS TO KEEP IN MIND:

§  Sequence values are not automatically protected after insertion in to a table. You should use Update Trigger on the table to prevent sequence values from being changed. §  Sequences 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 for example assigns values 1 through 100, and if the rows in the table grows beyond 100 rows, then Sequence 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.  Keep in mind that you can create sequence that are of any integer data type.  credits to Saleem Hakani.  my SQL Hero.

Verify SQL Backups!!!

       This is something everyone of us  give least preference and times feel this is not required at all, confident on our latest backup taken.. How many of us  faced the embarrassing situation on the D day when we need to restore the a  latest  backup available and  to our surprise,  BACKUP does not work.
         Although not required, verifying a backup is a useful practice. Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it. It is important to understand that verifying a backup does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup

use  RESTORE VERIFYONLY
Restore verifyonly  Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. More info on  RESTORE VERIFYONLY can find on below URL.

 http://msdn.microsoft.com/en-us/library/ms188902.aspx

BEST PRACTICES: LOCK DOWN YOUR SA ACCOUNT/PASSWORD

            Many of the services we run within Microsoft uses Windows Authentication. However, there are some services that still uses Mixed Authentication. If you are allowing SQL Server standard logins for access to your SQL Server, then 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 to try connecting in to SQL Server as the account is now renamed or is invalid.  

 You can easily disable or rename an SA account in SQL Server using the following steps:

 Note: This was not possible in earlier (prior to SQL2K5) versions of SQL Server.

RENAME SA ACCOUNT

ALTER LOGIN SA WITH NAME = [NEW_ACCOUNT]; GO

DISABLE SA ACCOUNT

ALTER LOGIN SA DISABLE; GO  credits to Saleem Hakani. my SQL Hero.

Some of the best practices for shrinking T-Log files.

Hi  all,

                There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log files within the log determines the possible reductions in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. Also, the log file is reduced in increments equal to the size of the virtual log file size. For example, a transaction log file of 1 GB can consist of five virtual log files of 200 MB each.

              Shrinking the transaction log file deletes unused virtual log files, but leaves at least two virtual log files. Because each virtual log file in this example is 200 MB, the transaction log can be reduced only to a minimum of 400 MB and only in increments of 200 MB.

     To be able to reduce a transaction log file to a smaller size, create a small transaction log and let it grow automatically, instead of creating a large transaction log file all at once.

        In SQL Server 2005, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to reduce a transaction log file to the requested size (subject to rounding). You should backup the log file before shrinking the file to reduce the size of the logical log and mark as inactive the virtual logs that do not hold any part of the logical log. For more information, see Shrinking the Transaction Log.

Best Practices

Consider the following information when you plan to shrink a database or file:

1) A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

 

2) Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

3) A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.

4) Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

Thanks,