How to make Identity Column in Existing Table.

We got a request to make column IDENTITY column in the existing table.

This is the process we followed.

1. Script-out the  table and index structure.

2. Rename the table using SQL syntax

EXEC sp_rename 'Old_Table_Name', 'New_Table_Name'


3.Run the scripted SQL from old table in step-1 and & make the column  DB_ASC identity column before running statement

CREATE TABLE  [D_ASC](
[DBAS_ID] [varchar](30) NOT NULL,
[DBC_AS_ID] [varchar](10) NOT NULL,
[DB_ASC ] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [X_AS_DB] PRIMARY KEY CLUSTERED
(
[DB_ASC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DB_Data]
) ON [DB_Data]
 GO

4. Run the  Import/Export wizard.

Right click on database where we have New table ----> Tasks ----> Import

5. Data Source will be the server/database which have old table

 Connect to source server and DB name where Old table exists. Click Next

6. Choose Destination Server/DB name  and Click on Next button

 7. select first option and click on next button.

8. Choose source and destination tables & Click on "Edit Mapping" Button.
On edit mapping window, Check the "Enable Identity insert" Box else we will get error message while data transfer from source table to destination.

9. Now click Next - Next and Finish Button. data will be transferred to new table with Identity column values.

How to Find SQL Server Version,Edition,Product Level

1. Select @@version

2. Run below Query in SSMS

Select
Case
When Convert(Varchar(128), ServerProperty ('ProductVersion')) like '9%' Then 'SQL Server 2005'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '10.0%' Then 'SQL Server 2008'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '10.5%' Then 'SQL Server 2008 R2'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '11%' Then 'SQL Server 2012'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '12%' Then 'SQL Server 2014'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '13%' Then 'SQL Server 2016'
Else 'Unspecified'
End as Version,
ServerProperty('Edition') as Edition,
ServerProperty('ProductLevel') as ProductLevel,
ServerProperty('ProductVersion') as ProductVersion

SQL Server 2014 Installation

SQL 2014 software downloaded from Microsoft site and now we will learn the installation.

I downloaded the ISO file,So I have mounted it.Open the disk where s/w mounted and right click on setup.exe, select "Run as Administrator".

It will open new window. from the below windows , select Installation option. click on  New server stand-alone .

New Windows will open, We will click on next button since we installing evaluation edition which is valid for 180 days.

On next page, check "I accept the license terms" box and click next

Global rules status should be passed. if anything fails,fix that before proceeding .Click Next if all green.

I am not checking Microsoft update check box , Click on Next button.

Install rules should be passed, we can ignore firewall warning.Click Next

From Set-up rules, select 1st option(SQL server feature installation).if u want to install all feature with default, choose 3rd option. choose 2nd option for share-point. Click next

choose whatever features u want to install from below page.Click  next

All feature rules should be passed.

Instance Configuration :
Default instance: we can have only one default instance per server and its name will be the name of  server  .we can connect to default instance using server name,local host or using dot(.) in SSMS.
Named Instance: we can have multiple named instance per server. in order to connect to named instance we should use [server-name\Named-Instance] Name in SSMS.
 I am going to install named instance "TEST" . Click on Next button.

Server Configuration:
we select how our sql server services and agent services run. Domain account must be used to run SQL server services and sql agent services. since i am installing it on my local machine,i am ok with default options.
Collation: it's simply rules to compare and  sort order,string,letters in SQL server.
SQL_Latin1_General_CP1_CI_AS is default collation in SQL server. Click on next

Database Engine Configuration:

1. Server configuration tab:

Windows authentication: only domain users (Windows) can connect to SQL instance
Mixed mode: Windows and SQL users  both can connect to SQL instance.

When we select Mixed mode, we have to type in the password for SA account.SA account have unrestricted access to SQL instance. MS recommends to disable the SA account.

Now we have to add the SQL admin group which will have full privilege on SQL server.
Click on ADD   then type the DBA admin group.

Now Click on "Data Directories " tab

For Better I/O performance:

1. Keep System DB on separate drive.
2. user database data files should be placed on separate drive
3. user database log files must be on different disk
4. Temp db should be on dedicated drive

Click on Next

Feature Configuration rules must be passed.Click on next

On Ready to install page, click on Install button & view the installation progress.

Finally SQL server is installed.

Click on Close Button.

Open SSMS and connect to SQL named instance.

SQL Server Reporting issue -- What to look at first glance

We had a issue with our reports since  users were not able to access the reports.

We checked the reporting log files and got the actual issue .

Connect to your Report server and explore the path & open the latest report log file

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles

Error:

Logon attempt for user 'svc_ssrs_report' failed., Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed.
Ensure the user name and password are correct. ---> System.ComponentModel.Win32Exception:
The referenced account is currently locked out and may not be logged on to
at Microsoft.ReportingServices.Diagnostics.Utilities.LoginUtil.Login(String userName, IntPtr ptrPwd, String domain)

Resolution:

Reporting user was unlocked by Windows team and account policies were changed so it never locked-out again.

Cluster Index with Drop Existing

CREATE CLUSTERED INDEX cixEmp
on Employee_InfoV2(emp_id)
with
(
DROP_EXISTING = ON,
sort_in_tempdb = ON,                 
FILLFACTOR = 80,         
PAD_INDEX = ON
)


Clause :

DROP_EXISTING = ON ----> if index exists, drop it  then recreate.

sort_in_tempdb = ON  ----> sort index operation in tempb

FILLFACTOR = 80 ---->fill each index page 80%. remaining 20% free,

PAD_INDEX = ON ----> If  Fillfactor is 80% ,then  each data page(Intermediate page in B-tree) will be  80% full remaining 20% free.

JOIN OPTIONS IN SQL SERVER

WHEN TO USE WHICH JOIN OPTION FOR FASTER JOIN OPERATIONS?



MERGE JOIN: APPLICCABLE FOR LARGER TABLES

LOOP JOIN: APPLICABLE FOR SMALLER TABLES

HASH JOIN: APPLICABLE FOR TABLES WITH UNKNOWN SIZE PARAMETERS

SELECT * FROM SALES_DATA
JOIN
PRODUCTS_DATA
ON
PRODUCTS_DATA.ProductKey = SALES_DATA.ProductKey
OPTION (MERGE JOIN)

SELECT * FROM SALES_DATA
JOIN
PRODUCTS_DATA
ON
PRODUCTS_DATA.ProductKey = SALES_DATA.ProductKey
OPTION (HASH JOIN)