Mustafa EL-Masry 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

Mustafa EL-Masry Configuration, Configuration

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

Mustafa EL-Masry Introducation

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

Mustafa EL-Masry Script

How to know SQL Server version


select @@servernameASServerName

Select @@VERSIONAsSQLServerVerjion

go

selectserverproperty(‘Edition’)AsServerEdition

selectserverproperty(‘ProductLevel’)ServerProductLevel

selectserverproperty(‘BuildClrVersion’)BuildVersion

Mustafa EL-Masry Administration, Errors

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