MySQL - AUTO_INCREMENT attribute

In the MySQL database, a sequence is a list of integers as unique number to produce in the ascending order. The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

• Column where set AUTO_INCREMENT attribute is typically primary key and it must be indexed as either primary key or unique index.
• One table can have only one AUTO_INCREMENT attribute.
• AUTO_INCREMENT attribute must have NOT NULL constraint even when you create table then MYSQL add NOT NULL by default into column.
• During INSERT MySQL assigned sequence numbers automatically into AUTO_INCREMENT attributed column.

Exercise

Create table without mentioning NOT NULL:

CREATE TABLE `employee` (

  `emp_id` int(11) AUTO_INCREMENT,

  `emp_name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`emp_id`)

);

Insert statement without mentioning emp_id col:

INSERT INTO employee(emp_name) VALUES ('John Hunt');

INSERT INTO employee(emp_name) VALUES ('Donald Trump');

INSERT INTO employee(emp_name) VALUES ('Obama');

You can explicitly assign 0 to the AUTO_INCREMENT attributed column to generate sequence number:

INSERT INTO employee(emp_id,emp_name) VALUES (0,'John Hunt');

INSERT INTO employee(emp_id,emp_name) VALUES (0, 'Donald Trump');

INSERT INTO employee(emp_id,emp_name) VALUES (0,'Obama');

You can assign NULL to the AUTO_INCREMENT attributed column to generate sequence number as column is declared is NOT NULL:

INSERT INTO employee(emp_id,emp_name) VALUES (NULL,'John Hunt');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Donald Trump');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL,'Obama');

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID().

Reset AUTO_INCREMENT

INSERT - When INSERT any other value instead 0 or NULL then sequence is reset upto that value and next auto generated value followed by sequence:

INSERT INTO employee(emp_id,emp_name) VALUES (10,'George Bush');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Sheron');

ALTER – You can set value with ALTER TABLE statement like:

ALTER TABLE employee AUTO_INCREMENT = 20;

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Raman');

Reset after table backup and TRUNCATE data

In such use case where you need to backup table data and truncate table to make table lightweight but do not want identity column start again from 1 as truncated.

• Backup table from available tools.
• Check and save last value of AUTO_INCREMENT table column. For ex. its 3888763.
• ALTER AUTO_INCREMENT table column.

ALTER TABLE employee AUTO_INCREMENT = 3888763;

All suggestions are most welcome. 

SQLServerGeeks Summit 2015 #SSGAS2015 - Journey

First I want to thanks Amit Bansal and SQLServerGeeks team for #SSGAS2015 and its great success.

My Journey start from August 27, 2015 when I was leave home from Greater Noida to Bangalore. It was already planned that I was staying with my closest friend Abhishek Dixit in Bangalore so no tension for accomodation but travel is one of the main part of journey.

Here I want to thanks my company DTA Inc. who sponcered this trip for me.

Yes I am talking about travel as I had already go through on Google map like where is Whitefield and where is Nimhance Convesion Center in Bangalore so I booked a Meru cab from Bangalore airport to Whitefield. It was wonderful Spicejet flight from Indira Gandhi International (IGI) Airpot to Kempegowda International Airport, Bengaluru (KIAB).

When I was in flight then I thought about #SSGAS2105 like what I would achieve and what I would have when return.

Now time arrived when I reached Nimhance Convention Center on time and feel energy which comes from SQL Server Geeks all team. I met my Delhi friend Suman Jha and feel more apna-pan in Bangalore city.

There was a wonderful and organized, alphabetical way to collect SSGAS kit and meal coupons. Now we are ready with all instruments. Now time to enter into HYDRA hall for keynote and stage looks all set to welcome T.K. ('Ranga') Rengarajan, Microsoft.

T.K. “Ranga” Rengarajan leads the Data Platform team, as part of the Cloud and Enterprise Division at Microsoft. As a Corporate Vice President for Data Platform, he is responsible for the Database and Big Data businesses. It was a really knowledgeable and presentable key by Ranga Sir. I had the privilege of attending Ranga’s keynotes.

After keynote we were full charge with positive energy and enthusiasm. There will be 20 session on day one and we can attend 5 as it was parallel 4 sessions and it was our choice. there was 4 halls which names was treat to SQL Server code names. 

Now fields are open and lot of well-known speakers session was wait on first day. Praveen Srivatsa, Raj Chaudhury, Satya Shyam K Jayanty, Lara Rubbelke, Denny Charry and Sunial Agarwal and many more great speakers session but we had a choice to choose what exactly we want. :(

On breaks we were met to each other and tried to make good understanding to everyone. I was met another friend called #SSGAS2015 friend Binay Prabhakar and I am very happy to say we are now great friend.

Taken lot of selfies and pics and also continue to share on Twitter @neerajsql as I know there was a price on tweet with hash tag #SSGAS2015 and #SSGASSelfie. :) few are here:

Keynote by me on SSGAS:

I think only Google is not enough to learn on better way or I can say there is lot of things/notes/tutorials are available in the world of internet but another important thing is what we need to learn. We can't be a master in everything so better to choose whatever we like. Seminar, Conferences, Meetups, Networking are the realistic things where we can learn about what is right path and what we need to choose. 

SQL Server Geeks team gives us such wonderful opportunity where we can choose the right things for himself not someone else. Take choices is the toughest things as I feel and we all are wait sometime to someone tell us yes this is right and this is wrong.

Here I can say #SSGAS2015 gives exact idea about what's going on and what will be the future rest we can learn from net and other trainings.

Exposure and feel more responsibility is another achievement from summit and focus is one of the most realistic things to get 100%. Oh great I write lot of thing in my Keynote. :)

It was a great experience to all around SQL Server and Microsoft world.

Learning from #SSGAS2015 - Now I also mentioned some of my key areas where I learned a lot and future planning as well.

SSRS 

Reporting services being used to produce some unusual and interesting kind of reports, including trellis charts, heat maps and dynamic infographics.

• Create grouping into reports, it would be row or column basis. 
• Use charts and map to make report interactive.
• Use active directory account with report server security.

SQL Server 2014 and beyond

SQL Server 16 enables customers can run analytic queries on in-memory and disk-based OLTP tables with minimal impact on business critical OLTP workloads, requiring no application changes. 

Query optimizer is used in In-Memory OLTP by both natively compiled stored procedures and by traditional stored procedures accessing memory-optimized tables through the query interop functionality. 

• Real time analytics with non clustered column store index for high end OLTP.
• Real time analytics with clustered column store index for low end OLTP.
• Can be minimizing latches and locks in In Memory feature.

BigData

it’s time to learn SAQL (Stream Analytics Query Language), the query language that gets data form devices, sensors, web sites, applications, static data, historic data, social media, and infrastructure systems to name a few, allowing you to do real-time analytic computations.

• MS working into Polybase feature and available on SQL Server 2016 CTP. 
• Where we can interact Hadoop database with TSQL.
• Polybase will have some feature similar like Hive. 
• Can be create analytics project with help of Hadoop, Hive and use Excel or Tableau for visualization where we can use Powershell to execute hive queries on windows plate form and   use putty on Linux plate form.
• Twitter and google are provide some data for analytics.
• Hive are supporting SQL language and we can also create hive UDF to use with hive. Hive       works on top of Hadoop.
• Azure Stream Analytics Query Language is subset of TSQl and can be use into IOT.

Performance Tuning

The SQL Server Query Optimizer is a cost-based optimizer: it analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans, and selects the plan with the lowest cost.

• Constraints also play important role into query optimization. Optimizer can ignore table scan if required constraint are there like wherever we want to disallow in where clause.
• Parameter sniffing can be reduced with Optimize parameter.
• Resource Governor – Can be use this tool with SQL Server to divide CPU and Memory resource as per requirement. Like Application will always need more CPU and Memory so we can give for ex 80 % to prod application and rest for reporting and other users.

So time to come for The End for this journey and I also mentioned there was great lunch I had on everyday with nice arrangement. Good food are also an essential part of success :). and last but not lease there was 2 surpises for everyone, great fun and enjoyed alot with beat boxing and Illusion show.

Oh yes I won a laptop bag for hash tag #SSGASSelfie. Thanks for my cell battery who help me a lot to win this :)

Now few more pics from closing ceremony.

Thanks again for this wonderful, awesome summit. #SSGAS2015 Rocks. Also A very big thanks to Sponcers.

SQL Server - Reporting Services got error message like InvalidSubscriptionException: An invalid subscription was found.

Hello,

During the report migration done database and encryption key backup and restore task successfully from 2008 to 2012 instance. But when check subscription on 2012 then not found.

There is no subscription showing into report server URL but checked all subscription is in reportserver database. I got following error message on SQL Server Reporting Services report log when migrated report server from 2008 R2 version to 2012 version.

Throwing Microsoft.ReportingServices.Library.InvalidSubscriptionException: , Microsoft.ReportingServices.Library.InvalidSubscriptionException: An invalid subscription '9999FD7C-C505-4827-AB30-42EBCA0A33C7' was found. Subscription must be deleted and recreated.

Not exactly found any solution for that then I just delete reports on Report Server and also cross check corresponding jobs into SQL Server Agent and also deleted there if found. Also update domain host name into rssqlserver config file as:

<DefaultHostName>yoursmtpserver.com</DefaultHostName>

Then I do backup and restore steps again and yes this time subscription are showing there.   

May this helpful OR please share if anyone found more.

Thanks