Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,156,127 hits

  • Select GETDATE()

    February 2026
    M T W T F S S
     1
    2345678
    9101112131415
    16171819202122
    232425262728  

Archive for the ‘SQL Server’ Category

« Previous Entries
Next Entries »

Did you know? You can generate Insert Statement with data using Management Studio!!!

Posted by blakhani on December 13, 2013


I must confess that at least I didn’t know about that option till I saw an internal email thread. And my first reaction was – Damn! all these years and never spotted that! Yes, we can get all of the INSERT statements for the data straight out of SSMS. Another hidden feature of SSMS.

Earlier, I used to search on internet and get some stored procedure to get the insert statements generated for me. Here were few search strings which used to give me proper results “SQL Generate Insert Script”, “SQL Server Generate Insert Script”, "SQL Server Script Insert Statements", "Create Insert Script SQL Server", "Generate Insert Script from Table"

Now I can avoid all those searches and do that right from the Management Studio. For demo purpose I would create a small database and show that option. Please note that I am using SQL Server 2012 Management Studio (SSMS 2012)

CREATE DATABASE [myAppDB]
go
Use myAppDB
go
Create table HelloSQL (iID int, vFname varchar(100), vLName varchar(100))
go
insert into HelloSQL values 
(1, 'Balmukund', 'Lakhani'),
(2, 'Vinod', 'Kumar M'),
(3, 'Pinal', 'Dave'),
(4, 'Sourabh', 'Agarwal'),
(5, 'Amit', 'Banerjee')
go
Select * from HelloSQL
go

 

To achieve our insert statements with script use Object Explorer and connect to an instance of the SQL Server. Expand Databases folder, Choose the database for which we want the script, right click, Tasks > Generate Scripts.

Once Wizard is launched, its self-explanatory to navigate through screen.

image

After welcome screen, we need to choose the object (table HelloSQL in our case) for which we want script to be generated.

image

In next screen, we need to choose the destination of script. I have selected “Save to new query window”

image

In the same screen, we have a magical button called “Advanced”. That’s where we have many options about scripting.

image

I have selected “Schema and Data” under “Type of data to script” and hit OK. Hit Next on previous screen

image

hit Next and Finish.

image

… and we have what we needed. Schema creation as well as Insert Statement for the data in table.

image

There are many other options in advanced tab. Play with them and get the desired script. I have learned this and it was new to me. Hope this is useful to you also. SQL Server generate script with data is a powerful SQL Server tool in order to create SQL script to move data from one database to another database. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.

 

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS, Step by Step | Tagged: , , , , , , , | 10 Comments »

A-Z of In-Memory OLTP : My name is Hekaton

Posted by blakhani on December 3, 2013


During my last session about In-Memory OLTP (Codename Hekaton) feature of SQL Server 2014 in SQLBangalore User Group Meeting, I discovered a lot of interest in the community about this. This has given me idea to share my knowledge about the feature via a blog series. This is first blog post of the series “A-Z of In-Memory OLTP” and hopefully it would help you to learn everything about In-Memory OLTP feature. My friend and regular blogger, Pinal (b|t) has written about it here. People have been using Hekaton and In-Memory OLTP interchangeably and I might not be an exception in my future post.

Hekaton is the code name used by SQL Server Product team for this feature. Once the product is released in market, feature gets a branding by marketing team. Hekaton is a Greek term for "factor of 100." – the aspirational goal of the project was to see 100 times performance acceleration levels. Another thing which you observe in my future posts is that product team have been using term XTP for the DMVs and other place which stands for eXtreme Transaction Processing. This feature is already announced to get shipped in the next major release of  SQL Server, which is SQL Server 2014.  While writing this blog, I have used CTP2 version which is free to download from here.

There have been myths about the new feature which are as follows:

  1. SQL Server In-Memory OLTP is a recent response to competitor’s offerings.
  2. In-Memory OLTP is like DBCC PINTABLE.
  3. In-Memory Databases are new separate products.
  4. You can use In-Memory OLTP in an existing SQL Server app with NO changes whatsoever.
  5. Since tables are in memory, the data is not Durable or Highly Available – I will lose it after server crash.

I can guarantee that ALL of the above are myths, which we would uncover in later part of this series. Here is the quick stab on above myths.

  1. Microsoft Research (MSR) team has been working on this since past couple of years to get this feature baked-in to the product . This was not an overnight decision to write code and make this feature. Research on these lines have been going on in SQL Server arena (Here, here and here). These links are for SQL Server 2012 days.
  2. It’s all together new feature which has completely new index and table structures. PINTABLE was only keeping (pinning) Table Pages in memory which was 8 KB in size and still has latch and locks taken for any modification. In-Memory OLTP is a latch (and lock) free engine.
  3. In-Memory database is not a separate product. This is part of SQL Server 2014 product itself (limited to Enterprise Edition x64 only – “Enterprise Evaluation” and “Developer” included). we need to install “Database Engine Services” component, during installation, to get this feature.
  4. Don’t trust someone if they say zero modification is required. At least, table schema needs modification to move disk based table to in-memory table. We would learn about syntax and internals later in the series.
  5. In-memory table does not mean they reside only in memory and hence non-durable. In-memory OLTP provides full durability for memory-optimized tables. When a transaction that has made changes to memory-optimized table commits, SQL Server, just like it does for disk-based tables, guarantees that the changes are permanent and can survive the database restart provided the underlying storage is available. We can have non-durable tables as well but by default, in-memory tables are durable and data would be available after server crash as well. (How? Well, you would learn more in further blogs on this series)

Why In-Memory

Looking at the market trends of tumbling cost of RAM (USD/MB) and performance implication of reading data from memory vs disk, its evident that people would love to keep the data in memory. With this evolution in hardware industry, softwares have to be evolved and modified so that they can take advantage and scale as much as possible. On the other hand, businesses also don’t want to compromise the durability of data – restart would clear RAM but data should be back in the same state as it was before failure (See myth # 5). To meet hardware trends and durability requirements, SQL Server 2014 has introduced In-Memory OLTP which would solve them in unique manner.

Microsoft already has some in-memory offerings namely xVelocity analytics engine and xVelocity columnstore index targeted analytical workloads and columnar storage, respectively. In words of Paul Larson, Principal Researcher with the Microsoft’s Database Group mentioned here -“In traditional models, the assumption is that data lives on disk and is stored on disk pages, This creates a lot of overhead when you try to access records. When data lives totally in memory, we can use much, much simpler data structures. Hekaton’s index data structures and storage structures are optimized on the basis that when a table is declared memory-optimized, all of its records live in memory.”

This is right time to remind you that In-memory is not applicable for complete database. So, we can say “hybrid-database”. You have option to choose tables which are hot and having contention and move them in-memory. This means that you can have a 1 TB database and have one hot-spot table of 100 GB in memory. Rest tables could be conventional in-disk storage.

More reading

Read this detailed technical paper about multi-version concurrency control (MVCC), written by product team and folks from University of Wisconsin submitted in Very Large Data Base Endowment Inc. (www.VLDB.org). You can read here about fantastic work done by Microsoft Research team.

If you haven’t yet, be sure to check out the SQL Server 2014 CTP2 so that you can follow this series and get your hands dirty.

Stay Tuned!

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, SQL Server, SQL Server 2014 | Tagged: , , , , , , | 15 Comments »

« Previous Entries
Next Entries »