This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.
In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.
Let us see the same concept in following SQL in Sixty Seconds Video:
Here is the script used in this episode:
USE tempdb
GO -- Create Table CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100)); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select data SELECT * FROM TestTable
GO -- Returns current Identity Value DBCC CHECKIDENT ('TestTable', NORESEED); -- Resets the current Identity value to maximum value DBCC CHECKIDENT ('TestTable', RESEED, 11); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select Table SELECT * FROM TestTable ORDER BY ID; -- Drop Table DROP TABLE TestTable;
Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.
Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager. Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.
Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?
I have downloaded NuoDBtoday and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.
Azure SQL database backup used to be a difficult task. Not any more. With SQLBackupAndFTP with Azure it became trivial. Here’s what you basically need to do:
Once SQLBackupAndFTP with Azure is installed, click at “Connect to SQL Server / Azure” button and specify connection properties for your Azure SQL Databases:
Then click “Run Now” to backup your Azure SQL Database(s):
Scheduling backups is also very simple – just check “Schedule this job on the main form” to run once daily or go to Advanced Settings for more options
Sounds simple? There are just a couple more things you need for this to work: you should know your Azure Server Name and Azure should allow your IP to connect. Let me explain how to do it.
How to find your Azure SQL Databases Server Name/Connection String
If you do not know a server name of Azure SQL Database, you can find this out on Windows Azure Management site. Sign in with your Microsoft account and click the “SQL DATABASES” menu item, then click your database name
Then click “Show connection strings” link in the bottom right corner:
You will see the connection strings for many platforms. Just copy to clipboard “Server” property value of “ADO.NET” connection like in the screenshot below:
And paste it into the “Server Name” field of the “Connect to SQL Server / Azure” window in SQLBackupAndFTP:
Allow your IP address to connect to your Azure SQL Databases server
For SQLBackupAndFTP to connect to Azure SQL Database, you need to configure Azure’s firewall. Otherwise you will be getting the error: Cannot open [server] requested by the login. Client with IP address [ip-address] is not allowed to access to the server…
Login to Windows Azure Management site with your Microsoft account, click “SQL DATABASES” menu item, then click your database:
Then click “Manage allowed IP addresses” link in the bottom right corner::
You will see a page where you can specify an allowed IP addresses for your databases:
On this page you can configure your firewall to allow the machine where SQLBackupAndFTP is running access to Azure. After adding the proper IP you should have no problem connecting to Azure.
Summary
With SQLBackupAndFTP with Azureit is very simple to take backups of Azure SQL databases as well as of regular SQL Server.
Sometime I feel I know a lot about SQL Server and very next moment, I realize that honestly I do not know much about this product. Earlier today, I had similar moments. I was playing with few DMVs and suddenly I ended up on the DMV sys.dm_xe_map_values. There are only four columns and one of the columns is a GUID.
The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.
If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.
SELECT dxmv.name, dxmv.map_key, dxmv.map_value FROM sys.dm_xe_map_values AS dxmv WHERE dxmv.name = N'statement_recompile_cause' ORDER BY dxmv.map_key;
When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.
map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed
It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
2007
Good, Better and Best Programming Techniques Well, here is my note which I prepared to discuss in my earlier meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.
Explanation SQL SERVER Merge Join
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
Random Number Generator Script – SQL Query
One of the most popular blog post where I demonstrate how many different ways one can generate random numbers. I have also built a SQL in Sixty Seconds Video on this subject as well.
SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
User Defined Functions (UDF) to Reverse String – UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain positions. First parameters take the string to be reversed. Second parameters take the position from where the string starts reversing.
Find Table in Every Database of SQL Server
This is one of the most asked T-SQL Script where user want to know where the table is used in every single database on the server. It is a two part story. Do not miss to read Part 1 and Part 2.
Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
SQL Server 2005 and later versions provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.
Solution to Puzzle – Shortest Code to Perform SSN Validation Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. This blog post was winner of the T-SQL Challenges on the same subject. If will be interesting to see if you can come up with a better optimized solution.
Example of DDL, DML, DCL and TCL Commands Data Manipulation Language, Data Definition Language, Data Control Language, Transactional Control Language explained in simple words.
Add or Remove Identity Property on Column How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action? For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. However, there is an easy way to accomplish this action. It can be done through SSMS.
Roadmap of Microsoft Certifications – SQL Server Certifications
There are several benefits you can gain after clearing certification exams. After passing the first exam, developers earn credential of Microsoft Certified Professional commonly known as MCP. Once credentials are achieved developers get access to an official transcript, logos and certificates, and have their own landing page on Microsoft.com! According to Microsoft’s official site, real MCPs get real benefits of Reward, Respect and Recognition.
2010
GUID vs INT – Your Opinion This is an age old problem and I want to compile the list stating the advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case). The epic intense debate is happening on this particular topic till today after 3 years. You can see there is a wealth of information on this blog which one can just learn reading the original blog post as well as comments associated with the blog post.
Disable Clustered Index and Data Insert
Should we disable the clustered index while we insert the large data or we should drop the clustered index while we insert large data. Honestly, the answer is very simple, drop the clustered index. If we disable clustered index the impact of it is much different than we expect. Check out this blog post which describes the same issue with working demo.
Here is one of my old photo of TechEd 2010 – I had great time presenting on the stage in year 2010.
What is AdventureWorks?
This question is always there in my mailbox. Till today, everyday multiple times. Here is the blog post where I describe where one can get AdventureWorks as well I explain how they can install it as well.
SHRINKFILE and TRUNCATE Log File in SQL Server 2008
There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it.
CTAS – Create Table As SELECT – What is CTAS?
CTAS stands for ‘Create Table As Select’. This method is used when table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.
Identify Columnstore Index Usage from Execution Plan How do I know if columnstore index is used by query through execution plan? Here is the detail blog post about how to identify if columnstore index is used in the query or not.
Here are the steps which are to be followed:
Create a sample table
Insert some data
Create clustered index on it
Create nonclustered Columnstore Index on it
Enable execution plan in SSMS
Run two SELECT statement together with using clustered index and columnstore index (use hint if needed)
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
