What is NEWSEQUENTIALID()

NEWSEQUENTIALID()

New NEWSEQUENTIALID generates new GUID.

The GUID generated is always greater than GUID that was previously generated.

Thus the new values inserted into tables using NEWSEQUENTIALID are in assending order and helps to fill index pages easyly.

NEWSEQUENTIALID is faster in operation than NEWID.

If a GUID Column in a table has to be bound with a unique Identifier then using NEWSEQUENTIALID is preferred.But the values can be predicted

NEWSEQUENTIALID cannot be used in queries like NEWID.

[sql] SELECT NEWSEQUENTIALID() [/sql]

The above query would raise an error:

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type ‘uniqueidentifier’ in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

Syntax for using NEWSEQUENTIALID with tables:
[sql]CREATE TABLE Table1 (Col1 uniqueidentifier DEFAULT NEWSEQUENTIALID()) [/sql]

How are values inserted into columns with NEWSEQUENTIALID?

[sql]CREATE TABLE Table1 (Col1 uniqueidentifier DEFAULT NEWSEQUENTIALID())
GO

INSERT INTO Table1(Col1)
VALUES (DEFAULT)

INSERT INTO Table1(Col1)
VALUES (DEFAULT)

INSERT INTO Table1(Col1)
VALUES (DEFAULT)

INSERT INTO Table1(Col1)
VALUES (DEFAULT)
GO

SELECT * FROM TABLE1
GO[/sql]

The output of the above query will be something like this:
07D7AE4D-97A3-E011-B424-0021971FCF00
08D7AE4D-97A3-E011-B424-0021971FCF00
09D7AE4D-97A3-E011-B424-0021971FCF00
0AD7AE4D-97A3-E011-B424-0021971FCF00

It can be seen that the values here are in sequence.

what is newid

What is NEWID() ?

NEWID Creates a unique value of type uniqueidentifier.

Its return type is uniqueidentifier.

[sql]SELECT NEWID() [/sql]

The output of the above query will be a unique identifier, some value like 8845D456-56FF-445Y-DF49-7GGTY54D3E3E

NewID can also be used to sort the rows in randomly as shown below

[sql]CREATE TABLE #TEMPNEWID(COL1 int)

INSERT INTO #TEMPNEWID(COL1)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6

SELECT COL1 FROM #tempnewid ORDER BY NEWID()[/sql]

The above query will give values in table #TEMPNEWID ordered randomly.

PUZZLE1 – What is the output of the below query NEWID

What is the output of the following query:
DECLARE @TEMPNEWID TABLE (iden int identity(1,1),COL1 int,COL2 UNIQUEIDENTIFIER)

INSERT INTO @TEMPNEWID(COL1,COL2)
SELECT 1,NEWID()
UNION
SELECT 2,NEWID()
UNION
SELECT 3,NEWID()
UNION
SELECT 4,NEWID()
UNION
SELECT 5,NEWID()
UNION
SELECT 6,NEWID()
UNION
SELECT 7,NEWID()
UNION
SELECT 8,NEWID()
UNION
SELECT 9,NEWID()
UNION
SELECT 10,NEWID()
UNION
SELECT 11,NEWID()
UNION
SELECT 12,NEWID()

DELETE T FROM
@tempnewid AS T
INNER JOIN (SELECT TOP 3 iden FROM
@tempnewid ORDER BY NEWID() ) as T1
ON T.iden = T1.iden

SELECT COUNT(*) FROM @tempnewid

How many records will be deleted from table variable @tempnewid?
What will be the output of count(*)?

Benefits and Features of SQL Azure

Benefits of SQL Azure:

  • High availability
  • High Fault tolerance
  • ability to create and deply of multiple number of databases
  • Similar to TSQL Used for SQL SERVER, hence makes its use easy
  • PAY AS YOU GO Option for pricing helps users pay for what they use
  • Integration to SQL Server and Visual Studio
  • Scale up and down the databases as per requirement
  • No need to take care of Physical administration of the server

Features of SQL Azure:

  • Cloud based model
  • Similar to SQL Server
  • Similar TSQL Usage
  • Ability to use tables, indexes, views,triggers, stored procedures, functions, roles etc
  • Execution of Joins on multiple tables
  • Transactions are allowed
  • Creation of Temporary tables is allowed
  • System functions like string,datetime are allowed
  • SQL Azure Data Sync

DIFFERENCES BETWEEN SQL AZURE AND SQL SERVER

SQL Azure is in very very similar to SQL Server in terms of TSQL and other common features in SQL Server. But there are few limitations of SQL Azure with respect to SQL Server.

SQL Azure does not support the following:

  • MASTER DATA SERVICES (Introduced in SQL Server 2008 r2)
  • SQL Server Utility
  • Common Language Runtime (CLR)
  • Service Broker
  • Database Mirroring
  • XML Indexing
  • Database Mirroring
  • Backup and Restore
  • Backup and Restore (SP’s from DLL’s)
  • SQL Server Agent
  • Replication
  • Table Partitioning
  • SQL Server Replication
  • Resource Governor
  • PolicyBased Management
  • Large User Defined Data Types
  • Full-Text Search
  • Data Compression
  • Extension methods USING CLR
  • Extension spatial types USING CLR
  • External Key Management
  • Sparse Columns
  • Transparent Data Encryption
  • Data Auditing
  • UDAs – User Defined Aggregates
  • Data Collector
  • FILESTREAM
  • Extended Events

WHAT IS SQL AZURE

WHAT IS SQL AZURE?

SQL AZURE is basically SQL Server Hosted in a cloud envirorment. This means it has the power of SQL Server and The power of Cloud Computing both held together.

SQL AZURE helps users Focus on their application and not the infrastructure required for running the application.

All the necessary Hardware, Software required for creation of SQL Server is taken care by the Cloud Service Provider.

By opting to SQL Azure the Hardware requirements and burden of hardware maintainence are moved away from the user and the user can concentrate on development of SQL Databases and hence enhances the ability of companies in creating SQL Code.
SQL Azure takes care of enviromental conditions like server setup, harddisk setup, processors etc and provides high availability, good levels of security.

SQL Azure Will for sure be the future of SQL Server databases as we see companies migrating from their self hosted servers to SQL Azure.

Users have high levels of options to opt SQL Azure in the Country Region where they wish to set up.

Users have to pay as they use and they would have options to reduce or increase their Disk spaces hence enhancing the Budget Capabilities of SQL Azure Users.

There are certain differences between SQL Azure and SQL Server hosted on local boxes. These differences are eloborated in the below link.
DIFFERENCE BETWEEN SQL AZURE AND SQL SERVER

SQL Azure was formally called as SQL Server Data Services and later it was called as SQL Services. SQL AZURE cloud based services are provided by Microsoft.

Advantages And Disadvantages Of Indexed Views

PROS and CONS Of Indexed Views:

Advantages Of Indexed Views:

  • To Improve the performance of select queries
  • Avoid Complex joins at run time and make the joined data readyly available
  • Index data on columns on multiple tables
  • The data from multiple tables is denormalized and helps in fastening the queries that fetch data
  • Joins that are frequently used can be converted into indexed views and thus reducing the query resopnse time of multiple queries
  • Advantage of having Higher Disk Space can be converted into having high query performance using Indexed views

Disadvantages Of Indexed Views:

  • Slows down the performance of the insert,update,delete statements on the tables used in indexed views
  • Increases the disc space used by the database as the views are created with physical data

Creating Indexed Views for Improving performance

Creating Indexed Views for Improving performance.

Indexed views are created in two steps

  • Creating View
  • Creating Indexes on the view

The syntax for creating the indexed view is different from that for creating a normal view.

Advantages Of Indexed Views:

  • To Improve the performance of select queries
  • Avoid Complex joins at run time and make the joined data readyly available
  • Index data on columns on multiple tables
  • The data from multiple tables is denormalized and helps in fastening the queries that fetch data
  • Joins that are frequently used can be converted into indexed views and thus reducing the query resopnse time of multiple queries
  • Advantage of having Higher Disk Space can be converted into having high query performance using Indexed views

Disadvantages Of Indexed Views:

  • Slows down the performance of the insert,update,delete statements on the tables used in indexed views
  • Increases the disc space used by the database as the views are created with physical data

Refer to the following link for complete list of Pros and Cons of Indexed Views:advantages and disadvantages of indexed views

Hence the decision of creating indexed views can be taken based on the following

  • Availability of disc space
  • Amount of performance improvement needed
  • Number of update,delete,insert quereies fired on the tables used in the indexed views

Please refer to the following MSDN Link to have further details about creating Indexed Views:
MSDN Link

Enable Mixed Mode Authentication

How to enable Mixed Mode Authentication in SQL Server?

Following are the steps to enable mixed mode authentication:

  • Connect to the server in object explorer
  • Right Click On The Server And click on properties
  • Now select Security Option in the “Select a page” Section
  • In the Server Authentication on the right side select “SQL Server And Windows Authentication Mode”
  • Click On Ok.
enable mixed mode authentication sql server
enable mixed mode authentication sql server

This enables both SQL Server authentication and windows authentication to access the SQL Server

Fetch current date time in SQL Server

SQL SERVER CURRENT DATE TIME

How To Retrieve The Current Date Time in SQL Server?

There are many in built functions provided by the SQL Server which can be used to fetch the current date time.
The date time that is fetched by these functions would be the date time of the server on which the SQL Server is installed. The time zone also corresponds to the Server On which the SQL Server is installed.

In order to fetch the current date time in SQL server the following Date and Time Functions can be used:

  • SYSDATETIME ()
  • SYSDATETIMEOFFSET()
  • SYSUTCDATETIME()
  • CURRENT_TIMESTAMP
  • GETDATE()
  • GETUTCDATE()
  • {fn NOW()}

Advanced Details:

All the above functions use the windows API GetSystemTimeAsFileTime() to fetch the current data time. The precision for this API is fixed to ~100 Nano seconds.

Date time Functions SYSDATETIME (), SYSDATETIMEOFFSET(), SYSUTCDATETIME() are high precition functions and the functions CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE()are low precision functions.

All the above functions have the same output in the query analyzer as shown in the below image:

datetime functions sql queryanalyzer
datetime functions sql queryanalyzer

This means using any of the above functions has the same performance impact.

Simply speaking, In order to get a high precision date time value then the function SYSDATETIME () can be used and for low precision date time value the function GETDATE() can be used.