RSS

Monthly Archives: February 2012

Sequences in SQL Server 2012

introducation

you can easily generate auto-incrementing numbers With Sequence, it will be a different object which you can attach to a table column while inserting

Creating a Sequence in SQL Server Coding

/****** Create Sequence Object ******/

CREATE SEQUENCE SEquence2012

START WITH 1

INCREMENT BY 2;

/****** Create Table ******/

Create  TABLE Employee

(  ID int NOT NULL PRIMARY KEY,

FullName nvarchar(100) NOT NULL  );

/****** Insert Some Data ******/

INSERT Employee (ID, FullName)

VALUES (NEXT VALUE FOR SEquence2012, ‘Mostafa’),

(NEXT VALUE FOR SEquence2012, ‘Mohamed’),

(NEXT VALUE FOR SEquence2012, ‘Elmasry’);

go

Creating a Sequence in SQL Server Wizerd

 
Leave a comment

Posted by on February 19, 2012 in New Feature

 

OFFSET … FETCH NEXT IN SQL SERVER 2012 RC0

This is New Feature in SQL Server 2012 i see this feature is very good .

OFFSET  command  —- provides a starting point for the SELECT statement , Form where the Select will begin

FETCH command   —– provides how many records to return at a time

in SQL Sever 2012

Use AdventureWork

GO

SELECT Name,ModifiedDate  FROM Production.Culture

ORDER BY name

offset 2 rows ——- Select the rows from tha table expected the 2 rows

FETCH NEXT 3 ROWS ONLY — show the first 3 rows from the result return after the OFFSET Command

IF you want to make this statment in Oldest Version you will make it like this

Use AdventureWork

GO

SELECT top 3 Name,ModifiedDate  FROM Production.Culture

where name not in (‘Arabic’,’Chinese’)

ORDER BY name

 
Leave a comment

Posted by on February 19, 2012 in New Feature

 

Dedicated Administrator Connection [DAC] in SQL Server

Introduction

DAC [Dedicated Administrator Connection ] is feature added from SQL Server 2005 version . Database administrator use this fetaure to connect to a SQL Server instance When the database engine not responding to the regular connection so the database administrator use this fetaure to connect to the instance to solve the problem Instead of rebooting  the SQL Server.

By Defult this feature is disable in All SQL Server version From 2005 to the New version 2012.

Note :  your SQL Server browser must be Runing

enable the DAC using below T-SQL command

Use master

GO

sp_configure ‘show advanced options’ , 1

GO

sp_configure ‘remote admin connections’, 1

G O RECONFIGURE

GO

enable the DAC using SSMS

1- write click on your SQL Server Probirties >> Factes >>  Service Area Configuration >> RemoteDacEnabled

Change it from False To True

Now try to connect by write ADMN: before your instance name like this

ADMIN:Mostafa-PC\SQLSERVER2008

then your User name and your password or connect windows Authentication but if you try to connect by the default way SQL Server will give you this Error

Dedicated administrator connections are not supported. (ObjectExplorer)

 

So if you want to connect with this Feature you must make this steps

Go to File >>> New >>> Database Engine Query >>>> Admin:InstanceName

 then your Session will open

Note : You can not open 2 Session

 
2 Comments

Posted by on February 19, 2012 in Configuration, Configuration

 

Difference between MBR and GPT in hard disk

Difference between MBR and GPT in hard disk

MBR:Master Boot Recorded

  • This option found in all operating  System
  • This option found if your disk type is basic not dynamic
  • You can setup any operating system 64bit or 32bit
  • You can make Dual booting because you have option make partition as active so you can setup more than one operation system in this hard
  • In windows XP  you can create 4 primary partition
  • In windows vista,7,2003 you can create 3 partition (Mandatory) and 128 logical partition
  • In windows 2008 can create 3 partition (Mandatory) and unlimited  logical partition
  • Support 3 file system Fat 16 , Fat 32 ,  NTFS
  • Mix mum Size of the partition is 2TB

GPT :GUID Partition Table

  • This option found in All operating system expected XP so in xp all hard disks if it basic it will be MBR
  • This option found if your disk type is basic not dynamic
  • Any operating system can running in this partition but must be 64bit if it 32bit will not running
  • You can’t make Dual booting because you don’t have option make partition as active like in MPR so you can’t setup more than one operating system
  • In windows 7,2003,2008 you can create 128 primary partition
  • You can’t create logical partition
  • Support one file system NTFS only
  • Mix mum size of the partition is 256 TB

 
2 Comments

Posted by on February 17, 2012 in Introducation

 

How to know SQL Server version

select @@servernameASServerName

Select @@VERSIONAsSQLServerVerjion

go

selectserverproperty(‘Edition’)AsServerEdition

selectserverproperty(‘ProductLevel’)ServerProductLevel

selectserverproperty(‘BuildClrVersion’)BuildVersion

 
1 Comment

Posted by on February 15, 2012 in Script

 

TRY…CATCH and ERROR Handling

CREATE TABLE TPA
(ID  INT , [NAME]  NVARCHAR(50))
GO
INSERT TPA VALUES (1,’MOSTAFA’)
INSERT TPA VALUES (2,’ELMASRY’)

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT * FROM TPA
END CATCH;
GO

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

 
Leave a comment

Posted by on February 15, 2012 in Administration, Errors

 

Repairing Suspect SQL Database

Before you run this script

  • Kill all connections and stop SQL Server
  • Perform a Checkdisk with repair option
  • Perform a Defragmentation
  • Now copy the files necessary (MDF, LDF and Backups)
  • Perform a backup
  • Restore the backup
  • Repair the Database (DBCC CHECKDB)
  • Backup the repaired DB

USE [master]
GO
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <dbname> SET SINGLE_USER
GO
DBCC CHECKDB <dbname>, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO
ALTER DATABASE <dbname> SET MULTI_USER
GO

Or

Take Care From this Script becouse it will make repaire and may be some Data will loss

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

 
2 Comments

Posted by on February 14, 2012 in Administration, Errors