Skip to main content

Posts

Showing posts from April, 2011

Keyboard Shortcuts for Query analyzer – SQL Server

Most common queries or tsql statements when we used most and worked more with them, then the best way is we should created keyboard shortcuts. Like select database properties, stored procedures text or top 10 records of tables. table serach, procedure or any object search i have created shortcut keys. It really help us to get work fast. It’s created as following, Let us find the database name contain word "db" using shortcut key '%db%' Pressing Ctrl + 4 and It will result set as below. Now look for the database property using keyboard shortcut . SparseDB Pressing Alt + F1 give us database property for above selected database . Looking for the stored procedures name started with “SP”. 'sp%' Pressing Ctrl + 5 and will return the name of the stored procedures as above condition. Same way we have object list which contain with ‘%tab%’. '%tab%' Pressing Ctrl+6 will give us the object names which we want. What you do

SEQUENCE Introduction in SQLServer 2011

It is very hardly when we need to retain sequence globally. We can use identity property of column but it is limited to that table only. SQL Server 2011 has introduced new feature named "SEQUENCE". We can retain the global sequence and can be used in multiple tables or in any queries. It's really global sequence generator and alternative option of Identity columns. Let's see the simple example using it. Creating database and table objests. -- Creating database CREATE DATABASE SEQUENCEDB GO USE SEQUENCEDB GO -- Creating table IF( OBJECT_ID('SequenceTab') > 0 ) DROP TABLE SequenceTab GO CREATE TABLE SequenceTab ( Seq_Id INT, Name VARCHAR(20) ) GO Now we will create a Sequence. -- Creating Sequence IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence') DROP SEQUENCE NewSequence; GO CREATE SEQUENCE [NewSequence] AS INT START with 1 INCREMENT by 1 MAXVALUE 1000 GO You can create sequence in Object Exp

ORDER BY Clause Enhancement and Pagination in SQL Server 2011

In earlier version of SQL server, when we need to SQL pagination then we were using temp table, Common Table expression or Ranking functions. One best feature introduced by SQL Server 2011 and that enhancement is with ORDER BY clause. Yes this is the feature introduced by SQL Server Denali and we can use this option to get the pagination. We can specify the Order by clause with OFFSET and FETCH NEXT keywords. Definition looks as following. OFFSET : Determine the start row to begin on. FETCH NEXT : How many rows need to fetch. Let's look on some examples of ORDER BY clause with OFFSET and FETCH NEXT. Creating Database and table objects. CREATE DATABASE PaginationDB GO use PaginationDB GO IF (OBJECT_ID('PaginationTable') > 0) DROP TABLE PaginationTable GO CREATE table PaginationTable ( PageId INT, PageName varchar(100) ) GO Creating Sequence IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence') DROP SEQUENCE NewSequence; GO CREA

Cycle Clipboard Ring - Interesting Feature of SQL Server Denali

SQL Server 2011 introduced with new and best feature. This new feature is "Cycle Clipboard Ring". Using this feature we can copy multiple texts and pate it sequentially with Ctrl + shift + V . Very amazing feature. Lets look on the below shots. -- First I am copying this text only USE MASTER GO -- Second I am copying this text only SELECT name, database_id, compatibility_level, state_desc,is_read_only FROM SYS.DATABASES GO -- Third I am copying this text only SELECT * FROM SYS.OBJECTS GO Now these three copied items are on the clipboard and you can paste in sequentially. To paste first item which you copied, press Ctrl + Shift + V  1 time . To paste second item which you copied, press  Ctrl + Shift + V  2 times . To paste third item which you copied, press  Ctrl + Shift + V  3 times . Please try with above example and enjoy clipboard items!

Can we install Multiple Versions of SQL Server in same Machine?

I was asked from so many people by one question, "Can we installed multiple versions of SQL Server in same machine?" and complained about about errors while installation of multiple versions. What's your answer? My answer is Yes. Here is the snap which my machine running multiple versions of SQL Server 2005, SQL Server 2008 and SQL Server Denali. Let's look on below shot 1. 2. Please share your experience for this , if you got any errors or issues.

Installation Enhancement in SQL Server Denali.

In earlier post I wrote the all the SQL Server 2011 (Denali) new features and enhancements and i promised to write the post in details for the new features. I have already posted the article in earlier post, how to install SQL Server 2011. I hope you have already downloaded and installed it. Today i am going to write for the new enhancement of SQL Server Denali installation. Prerequisite for the SQL Server installation : Here all the prerequisite will be installed during SQL Server setup except Windows Power shell 2.0 ( how to enable in windows ), .Net Framework, No-Reboot package for .Net Framework 4.0 as it will not installed by SQL Server setup , you can download it before the setup. From where you can download? here Windows seven and Windows server 2008 R2  and Windows vista and Windows server 2008 . SQL Server Multi-Subnet clustering : With this new version of SQL Server CTP , we can configure fail-over clustering using cluster node on different subnet. See more informa

SQL Server 11 - First look and feel

I have successfully downloaded SQL Server 2011 CTP1 and installed in my machine. I am very eager and waiting to open the it as how it look. I have opened it first time and feel better. I am sharing you some of the snaps which i have taken during my look. 1. Startup screen. 2. Login screen. 3. Object Explorer look. 4. Query Analyzer look. What's your first feel for SQL Server 2011?. Share it.

Installation of SQL Server 2011 (Code Named - Denali)

I am very happy today because I downloaded SQL Server 2011 (Denali) CTP1 and installed in my machine. Also getting started to write the post for the SQL Server Denali. Write now I want to getting started with the installation of SQL server 2011 as how can we install. Here are the some of steps and snaps which will drive us to finish the installation. 1. Planning to installed SQL Server. 2. Getting started and installation options. 3. Setup support rules. 4. Selection of the edition to be installed. 5. Setup support files. 6. Setup support rules and report for the same. 7.  Setup roles. 8. Future selection and select the options which want to install. 9.  Installation rules and i'ts report. 10. Instance configuration , Instance name and directory selection to be installed SQL Server.

What's new with SQL Server Code-Named "Denali".

Every readers want to know the new features and enhancements of new version of SQL Server.I am also, and also eager to blog the new features. New version is available for the preview for community which is called as SQL Server Code-Named - Denali. It is also known as SQL Server 2011. You can download this CTP at  http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 . I have downloaded the SQL Server Code-Named "Denali" Community Technical Preview (CTP) and started to learn new features of the Denali. Some of the new features are listed below. SQL Server AlwaysOn. T-SQL Enhancements. Changed Startup Option. Availability Groups(HADR). Contained Databases. Sequence. Fulltext Search Enhancement. New Collation Support. Data Integration Management. BI Enhancement. Juneau. Analytic Enhancement. IT Administration. Column  Based Query Accelerator. Ad-Hoc Query Paging. Code Snippets Manager. Task List. Zoom or Magnify.

Microsoft organizing Windows Azure camp in Ahmedabad

I am very eager for the Microsoft events in Ahmedabad. Now i heard from my ASP.NET MVP friend  Jalpesh Vadagama as the event "Microsoft Windows Azure" and this event camp will be on to 30th April 2011(postponed). But even i need to wait up to 30th April. The registration is open now. Fill up registration form for the Windows Azure Camp here,  http://www.microsoft.com/india/azurecontest/azurecamp.aspx Please don't miss for the registration and participation this camp as well. This will be not only in Ahmedabad, but will be held in various below cities. Also you have option to select the city and date schedule in registration form. Please share this information to your interested friends, so they will also get the chance to attend this camp.

Various ways to get TOP records from table - SQL Server

We confused sometimes during write the code logic for SQL queries when we required some of top records from tables to implement in logic. This is very basic thing for the development and here i am going to demonstrate some of the options to get top records using TOP clause  from table in various ways. -- Let's create table IF ( Object_id('TopRecords') > 0 )   DROP TABLE TopRecords GO CREATE TABLE TopRecords   (      Id      INT IDENTITY(1, 1),      Records VARCHAR(50)   ) GO -- Inserting records in table DECLARE @Cnt INT SET @Cnt = 1 WHILE ( @Cnt <= 100 )   BEGIN       INSERT INTO TopRecords                   (Records)       SELECT CONVERT(VARCHAR, @Cnt) + '_xyz'       SET @Cnt = @Cnt + 1   END GO #1 way -- Selecting top 1 records by declaring and assiging value to varible DECLARE @top INT SET @top = 10 -- set @top to 10 to get top 10 record from restult SELECT TOP (@top) Id,                   Records FROM   TopRecords #2 way -- Seletin

Filtered Index - New feature of SQL Server 2008

Sometime maintenance of the indexes are taking so much time and they are high costly. Becuase of that i have excluded some of the haviely indexes from script of indexs maintance as all the rows of those tables are not used in the stored procedures to be quelified in where condition. But the new feature of SQL Server 2008 - Filtered Index has resolved this problem. From this feature we can create index on table using where condition, means it will create an index on the quelified rows of columns. It's really best feature because it will solve problem of storage of indexs and as well maintenance of the indexes and will require less storage. It will most usefull where we don't require to create an index on all the rows or frequenty updated data. Let's see the some examples using "Filtered Index" feature and will check the execution plan using this index. Creating Objects. -- Dropping Indexes on the table IF (OBJECT_ID('Ix_FilteredDate_WithoutFiltered&#

Working with SPARSE column in SQL Server 2008

Recently during migration from SQL server 2005 to SQL Server 2008,  I have utilized one more feature of SQL server 2008 and that is SPARSE column. It is used to reduce storage data in tables, mostly when tables having 20-30 % NULL values.It is really amazing feature and we can reduce storage space as we can with SPARSE. Here are some demonstration with example. Creating database and table objects -- Creating database CREATE DATABASE SparseDB GO USE SparseDB GO -- Creating table without sparse column CREATE TABLE TablewithoutSparse ( Id int identity(1,1), SparseCol1 int, SparseCol2 varchar(100), SparseCol3 datetime ) GO -- Creating table with sparse column CREATE TABLE TablewithSparse ( Id int identity(1,1), SparseCo1l int SPARSE, SparseCol2 varchar(100) SPARSE, SparseCol3 datetime SPARSE ) GO Let us insert some records in tables. -- Inserting 55555 records (All NULL values) in both tables INSERT INTO TablewithoutSparse VALUES (NULL, NULL, NULL) INSERT INTO TablewithSparse VALU

Blog views - post outside of SQL Server

One day before at the time of login in www.blogspot.com , Suddenly i was notified by popup information as new views available for the blogs. I clicked to see the views and it's really nice. You can view you blogger views as /*your blogspot address*/view, like for my blog www.paresh-sqldba.blogspot.com/view . It will open five available following views 1. Sidebar 2. Flipcard 3. Mosaic 4. Snapshot 5. Timeslide You can open all above views by clicking on options or given view name directly after url. During enjoying with views i have captured various snaps which i am going to attach here for your enjoyment.                                 www.paresh-sqldba.blogspot.com/view/sidebar                                  www.paresh-sqldba.blogspot.com/view/flipcard                                  www.paresh-sqldba.blogspot.com/view/snapshot                                   www.paresh-sqldba.blogspot.com/view/mosaic                                   www.paresh-sqldb

"Powersum is not recognized built-in function name" error with comparing SQL Server 2005 and 2008 databases schema using Red Gate SQL Compare 6.0 tool

I was working to sync database schema between SQL Server 2005 and 2008 databases using Red Gate SQL Compare 6.0. While comparing the schema, it was sucessfull to read objects and permission for the SQL server 2005 database but encountred errors for the SQL Server 2008 database as following. I have received the support from SQL Red Gate team and it was due to this version is not supported to SQL Server 2008 database shema comperasion. Red Gate SQL Compare only supported SQL 2008 from version 7 onwards: http://www.red-gate.com/messageboard/viewtopic.php?t=7751   and  the PowerSum keyword is deprecated and was removed from SQL 2008. After upgrading version from 6 to 7 or later, I was working fine for me. We can enable logging for SQL Compare tool errors as following, So we can have more idea on error details.