Saturday 16 November 2013

SQL SERVER - Notes 7



49. Usage of Rule .
       Rule is a object which is used to specify a column acceptable values.
     
CREATE TABLE countries(NAME VARCHAR(3),code INT)
go

CREATE RULE countryrule AS @con IN ('US','IND')
go
EXEC sys.sp_bindrule 'countryrule', -- nvarchar(776)
   'dbo.countries.name' -- nvarchar(776)
   
go

INSERT INTO countries(name ,code)
VALUES ('Ind',545)
INSERT INTO countries(name ,code)
VALUES ('US',545)
INSERT INTO countries(name ,code)
VALUES ('UK',545)

EXEC sys.sp_unbindrule 'countryrule'

Note : This feature will be removed from the microsoft in the future release,so avoid this feature in the development work, Modify the feature
with check constraint.

Example
ALTER TABLE countries
ADD CONSTRAINT countryrule CHECK(name IN ('IND','US','UK'))


50. Usage of  sp_xml_preparedocument ?
            It reads the input xml and parsed the text using the MSXML Parser and returns the handle for the parsed document. this handle is valid for the duration of session or until the invalidated of handle using the sp_xml_removedocument

A Paresed document is stored internal cache of SQL SERVER. MSXML uses 1/8 of the total memory available for  the SQL SERVER.\


-- Save the xml document in the Cache of the Sql Server  ?
DECLARE @xmlstring VARCHAR(2000)
DECLARE @handle INT

SELECT @xmlstring = N'<Persons><Person><name>AB</name><age>25</age></Person><Person><name>Bc</name><age>26</age></Person></Persons>'
EXEC sys.sp_xml_preparedocument @handle OUTPUT,@xmlstring
SELECT * FROM OPENXML(@handle,'Persons/Person')
WITH (USERNAME  VARCHAR(40) 'name',age INT 'age')

EXEC sys.sp_xml_removedocument @handle


51. New Features available in SQL SERVER 2008 ?
  •       New DataType [Date, time, FileStream] 
         DECLARE @date DATE
         DECLARE @time TIME
  •       Compouned Operations [+=,-=,*=,/=]                
         DECLARE @i INT = 1
         SELECT @i+=2
         SELECT @i
  •       Merge Statement  
MERGE INTO [targettable] AS t
USING [sourcetable] AS s
ON t.[column] = s.[column]
WHEN MATCHED THEN
-- statements
WHEN NOT MATCHED THEN
-- statements
  •       Sparse Columns
  •       Table value parameters
  •       Integrated Full Text search
  •       Intellisense
  •       Transparent Data Encryption (Encrypt whole database explicitly)
  •       Resource Governor
  •       Policy Based Management
  •       Linq

52. What is sparse columns ?
         A Sparse column is a optimised storage format to store NULL values, we can specify a table column as sparse by SPARSE attribute. 

  •  It greatly reduces the space requirement for your table columns.
  • It doesn't take up any space when the column value contains the NULL.
  • It takes up more space when column contains NON-NULL value
  • It is suitable for only the columns which have storage value NULL in large percentage. 

CREATE TABLE employeetable
(
 id      INT IDENTITY(1,1),
 NAME    VARCHAR(40),
 addres  VARCHAR(200),
 tempaddres VARCHAR(300) SPARSE
            )

53. How to modify the value in XML ?

       Now below example will explain you how to change the value of an Hr to tech in an xml first tag.    [1]  Refers the occurrence of first element. 

       Xml Format:
<Emps>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>
  
DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SET @employeedata.modify('replace value of (/Emps/Emp[@dept=("HR")]/@dept)[1] with "Tech"')
SELECT @employeedata

Output: 
<Emps>
  <Emp dept="Tech">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
           </Emps>

54. How to insert a new tag in existing XML ?
      Now we see how to insert an new element in the existing xml , in different positions.
      Xml format:
<Emps>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>


DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SELECT @employeedata


--Insertion
SET @employeedata.modify('insert <Emp>b</Emp>  as first into (/Emps)[1]')
-- or
SET @employeedata.modify('insert <Emp>b</Emp>  into (/Emps/Emp)[1]') -- insert as last tag
SELECT @employeedata

<Emps>
  <Emp>b<Emp>b</Emp></Emp>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>


55. How to DELETE a Value from XML based on Condition ?
      Delete a element tag from the xml based on condition.


Xml format:
<Emps>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>


DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SELECT @employeedata


--Deletion
SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])') -- delete all HR records
SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])[1]') -- delete first HR record

SELECT @employeedata

Output:

<Emps>
  <Emp dept="tech">R</Emp>
</Emps>

56. How to find the existence of a value or tag in XML ?
       Execute a two different code , based on occurrence of value in element

--Exist
IF @employeedata.exist('(/Emps/Emp[@dept="HR"])[1]') = 1
BEGIN
  SELECT 'Employee Exist in HR dept'
END
ELSE
BEGIN
      SELECT 'No employee exist in HR Dept'
            END









Keyboard shortcuts

In this post let we see some of the shortcuts which will use when we are not have mouse , or keyboard alone connected in computer This will helpful in some handy situation.

More than 100 Keyboard Shortcuts ….

Keyboard Shorcuts (Microsoft Windows)


1.  CTRL+C (Copy)
2.  CTRL+X (Cut)
3.  CTRL+V (Paste)
4.  CTRL+Z (Undo)
5.  DELETE (Delete)
6.  SHIFT+DELETE (Delete the selected item permanently without placing the item in the                    Recycle Bin)
7.   CTRL while dragging an item (Copy the selected item)
8.   CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)
9.   F2 key (Rename the selected item)
10.  CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
11.  CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
12.  CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
13.  CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
14.  CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
        SHIFT with any of the arrow keys (Select more than one item in a window or on the                     desktop, or select text in a document)
15.  CTRL+A (Select all)
16.  F3 key (Search for a file or a folder)
17.  ALT+ENTER (View the properties for the selected item)
18.  ALT+F4 (Close the active item, or quit the active program)
19.  ALT+ENTER (Display the properties of the selected object)
20.  ALT+SPACEBAR (Open the shortcut menu for the active window)
21.  CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
22.  ALT+TAB (Switch between the open items)
23.  ALT+ESC (Cycle through items in the order that they had been opened)
24.  F6 key (Cycle through the screen elements in a window or on the desktop)
25.  F4 key (Display the Address bar list in My Computer or Windows Explorer)
26.  SHIFT+F10 (Display the shortcut menu for the selected item)
27.  ALT+SPACEBAR (Display the System menu for the active window)
28.  CTRL+ESC (Display the Start menu)
29.  ALT+Underlined letter in a menu name (Display the corresponding menu) Underlined letter in a command name on an open menu (Perform the corresponding command)
30.  F10 key (Activate the menu bar in the active program)
31.  RIGHT ARROW (Open the next menu to the right, or open a submenu)
32.  LEFT ARROW (Open the next menu to the left, or close a submenu)
33.  F5 key (Update the active window)
34.  BACKSPACE (View the folder one level up in My Computer or Windows Explorer)
35.  ESC (Cancel the current task)
36.  SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing)

Dialog Box - Keyboard Shortcuts


1.  CTRL+TAB (Move forward through the tabs)
2.  CTRL+SHIFT+TAB (Move backward through the tabs)
3.  TAB (Move forward through the options)
4.  SHIFT+TAB (Move backward through the options)
5.  ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
6.  ENTER (Perform the command for the active option or button)
7.  SPACEBAR (Select or clear the check box if the active option is a check box)
8.  Arrow keys (Select a button if the active option is a group of option buttons)
9.  F1 key (Display Help)
10.  F4 key (Display the items in the active list)
11.  BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

Microsoft Natural Keyboard Shortcuts


1.   Windows Logo (Display or hide the Start menu)
2.   Windows Logo+BREAK (Display the System Properties dialog box)
3.   Windows Logo+D (Display the desktop)
4.   Windows Logo+M (Minimize all of the windows)
5.   Windows Logo+SHIFT+M (Restorethe minimized windows)
6.   Windows Logo+E (Open My Computer)
7.   Windows Logo+F (Search for a file or a folder)
8.   CTRL+Windows Logo+F (Search for computers)
9.   Windows Logo+F1 (Display Windows Help)
10.  Windows Logo+ L (Lock the keyboard)
11.  Windows Logo+R (Open the Run dialog box)
12.  Windows Logo+U (Open Utility Manager)
13.  Accessibility Keyboard Shortcuts
14.  Right SHIFT for eight seconds (Switch FilterKeys either on or off)
15.  Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
16.  Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
17.  SHIFT five times (Switch the StickyKeys either on or off)
18.  NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
19.  Windows Logo +U (Open Utility Manager)
20.  Windows Explorer Keyboard Shortcuts
21.  END (Display the bottom of the active window)
22.  HOME (Display the top of the active window)
23.  NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)
24.  NUM LOCK+Plus sign (+) (Display the contents of the selected folder)
25.  NUM LOCK+Minus sign (-) (Collapse the selected folder)
26.  LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder)
27.  RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder)

Shortcut Keys for Character Map


After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:
1.  RIGHT ARROW (Move to the rightor to the beginning of the next line)
2.  LEFT ARROW (Move to the left orto the end of the previous line)
3.  UP ARROW (Move up one row)
4.  DOWN ARROW (Move down one row)
5.  PAGE UP (Move up one screen at a time)
6.  PAGE DOWN (Move down one screen at a time)
7.  HOME (Move to the beginning of the line)
8.  END (Move to the end of the line)
9.  CTRL+HOME (Move to the first character)
10.  CTRL+END (Move to the last character)
11.  SPACEBAR (Switch between Enlarged and Normal mode when a character is selected)



Microsoft Management Console (MMC)
Main Window Keyboard Shortcuts


1.   CTRL+O (Open a saved console)
2.   CTRL+N (Open a new console)
3.   CTRL+S (Save the open console)
4.   CTRL+M (Add or remove a console item)
5.   CTRL+W (Open a new window)
6.   F5 key (Update the content of all console windows)
7.   ALT+SPACEBAR (Display the MMC window menu)
8.   ALT+F4 (Close the console)
9.   ALT+A (Display the Action menu)
10.  ALT+V (Display the View menu)
11.  ALT+F (Display the File menu)
12.  ALT+O (Display the Favorites menu)

MMC Console Window Keyboard Shortcuts


1.  CTRL+P (Print the current page or active pane)
2.  ALT+Minus sign (-) (Display the window menu for the active console window)
3.  SHIFT+F10 (Display the Action shortcut menu for the selected item)
4.  F1 key (Open the Help topic, if any, for the selected item)
5.  F5 key (Update the content of all console windows)
6.  CTRL+F10 (Maximize the active console window)
7.  CTRL+F5 (Restore the active console window)
8.  ALT+ENTER (Display the Properties dialog box, if any, for theselected item)
9.  F2 key (Rename the selected item)
10.  CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Remote Desktop Connection Navigation


1.   CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)
2.   ALT+PAGE UP (Switch between programs from left to right)
3.   ALT+PAGE DOWN (Switch between programs from right to left)
4.   ALT+INSERT (Cycle through the programs in most recently used order)
5.   ALT+HOME (Display the Start menu)
6.   CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)
7.   ALT+DELETE (Display the Windows menu)
8.   CTRL+ALT+Minus sign (-) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
9.   CTRL+ALT+Plus sign (+) (Place asnapshot of the entire client window area on the Terminal server clipboardand provide the same functionality aspressing ALT+PRINT SCREEN on a local computer.)

Microsoft Internet Explorer Keyboard Shortcuts


1.   CTRL+B (Open the Organize Favorites dialog box)
2.   CTRL+E (Open the Search bar)
3.   CTRL+F (Start the Find utility)
4.   CTRL+H (Open the History bar)
5.   CTRL+I (Open the Favorites bar)
6.   CTRL+L (Open the Open dialog box)
7.   CTRL+N (Start another instance of the browser with the same Web address)
8.   CTRL+O (Open the Open dialog box,the same as CTRL+L)
9.   CTRL+P (Open the Print dialog box)
10.  CTRL+R (Update the current Web )


Friday 15 November 2013

SQL SERVER - Notes 6

36. What is the Difference between the Truncate and Delete ?
       Delete is a row by row execution process, It makes the row as lock to mark for delete. It have filter based remove of record based on where clause.It activates the trigger , to record the operation in Log. It can be rollback.DELETE is DML Command

    Truncate is used to delete all records , doesn't have the filter condition, Can't able to rollback, doesn't maintain the log file to maintain the records removal. Faster than Delete operation. Truncate is DDL Command.

37. Various ways to insert the records in to the table ?


INSERT INTO employee (name,age,salary)
VALUES('hh',28,230333)

SELECT * INTO newtable FROM employee

INSERT INTO newtable
(
name,
age,
salary
)

SELECT 'KA',26,393778


38. What is CTE ?
      CTE is known as Common table expression. Which have temporary result set have validity up to the next line of execution. there are various things is exists to store the temporary result set , Table variable, Table valued function and CTE. Now we see sample of CTE.

;WITH cte AS (SELECT * FROM employee)

SELECT * FROM cte


39. Sample of Table variable usage ?
      Table Variable have the scope with in the object execution,Afte that it can't able to access.

DECLARE @emptable TABLE
(
id INT ,
NAME VARCHAR(30)
)

INSERT INTO @emptable
        ( id, NAME )
SELECT id ,NAME
FROM Employee


SELECT * FROM @emptable


40 what is the usage of UNION ?
      Union is the keyword which is used to concat the values of two or more tables which have the same number of columns with same alias name. Result set have distinct rows it avoids the repeatation of records.In this example we are selecting a two table with union , but result set have only one record.

SELECT * FROM employee
UNION
SELECT * FROM employee


output:

id          NAME                           age         salary      department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1           KA                             NULL        NULL        NULL


41. Difference between the UNION and UNION ALL ?
        Union Returns the unique row of result set. Union ALL gives the all records from the two tables. i.e when executing below code we are getting two records.

SELECT * FROM employee
UNION ALL
SELECT * FROM employee


output:
id          NAME                           age         salary      department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1           KA                             NULL        NULL        NULL
1           KA                             NULL        NULL        NULL


42. Sample of ISNULL, NULLIF, EXISTS , IN, NOT, WHILE, IF ELSE, SWITCH CASE

-- used to check if the first parameter of the function is null then second paramter of the function is returned as value
SELECT ISNULL(age,28) FROM employee

--used to check whether the both parameters passes are same consider to be null otherwise first parameter
-- is a column second parameter is -1 that indicates if -1 occurs treated it as null value
SELECT NULLIF(age,-1) FROM employee

-- Exist keyword is used to check whether the exist of a record based on particular condition
IF EXISTS(SELECT name FROM employee WHERE id = 1)
BEGIN
 PRINT 'Employee is working in this organisation'
END

-- In condition is used to fetch records in that condition present in filter
SELECT * FROM Employee WHERE id IN (1,2,4)

-- Not is used to fetch the records other than specified condition.
SELECT * FROM Employee WHERE id NOT IN (1,2,4)

-- While is loop execute a statement based on condition
DECLARE @i INT =1
WHILE @i < 8
BEGIN
      PRINT @i
      SELECT @i = @i + 1
END

-- If Else Condition
DECLARE @i INT = 1

IF @i = 2
BEGIN
      SELECT 'value is 2'
END
ELSE
BEGIN
      SELECT 'value is not 2'
END

--Switch case statement
DECLARE @i INT = 1
DECLARE @result VARCHAR(30)

BEGIN

      SELECT @result = CASE
                                    WHEN @i > 5 THEN  'Greater than 5'
                                    WHEN @i <5 THEN  'Less than 5'
                                    ELSE
                                    'None'
                              END
      SELECT @result

END


43. How to create a Transaction in SQL SERVER ?
         Transaction is used to record the sequence of steps and maintain a consistent finish of operation based on execution statement if operation reaches the final statement then we can commit the changes to the table , otherwise we can rollback the changes .

example

BEGIN TRY
BEGIN TRAN

DECLARE @i INT

SELECT @i = id FROM employee WHERE name = 'KA'

IF @i IS NOT NULL
BEGIN
      SELECT 'transaction commited'
      COMMIT TRAN
END
ELSE
BEGIN
      SELECT 'transaction rollbacked'
      ROLLBACK TRAN
END

END TRY
BEGIN CATCH
      SELECT ERROR_MESSAGE() 

END catch

44. How to use a Try and Catch Block ?

BEGIN TRY
     
      SELECT CAST('d' AS INT)   -- Predefined cast error
     
      RAISERROR('error on cast ',16,1) -- User defined raise error
     
END TRY
BEGIN CATCH

      SELECT      ERROR_MESSAGE() AS 'Mesage'   ,
                  ERROR_LINE() AS 'Line no',
                  ERROR_NUMBER() AS 'Error number',
                  ERROR_SEVERITY() AS 'Severity',
                  ERROR_STATE() AS 'State'
     

END catch


45. what is usage of Merge ?
       Merge is like a Join used to join two tables and based on the condition , if the condition match then for particular we can create a login for target table, When condition not matched we can create a another logic.

MERGE INTO EMPLOYEE AS E
USING EMPTABLE AS T
ON    E.id = T.id
WHEN MATCHED THEN
      UPDATE SET E.NAME = T.NAME
WHEN NOT MATCHED THEN
      INSERT(NAME,age)
      VALUES(T.NAME,T.AGE);


46. How to retrieve a Value from an XML and insert into table ?
     Click Here to Read

47. What is Collation in SQL SERVER ?
    Collation is a set of rules applied for the proper use of character for either a language in the server, for comparing and sorting, It have case sensitivity, Accent sensitivity and khana sensitivity


-- Employee table have name Raj, rAj, raj, RAJ with differnet case sensitivity of same name

SELECT * FROM employee
WHERE name = 'Raj'

-- Above query results four records, To fetch the correct records as match exact the same case sensitivity
-- Collate should be added in where condition

SELECT * FROM employee
WHERE name COLLATE Latin1_General_CS_AS  = 'Raj'

-- permanently added the collate in table column
ALTER TABLE employee
ALTER COLUMN name COLLATE Latin1_General_CS_AS

EXEC sp_help PersonDB

-- To fetch collation of any column in any table from the database

48. What is User defined Data Type ?
    User defined Data type is a type which can be created from the base type, and make the end user not knowing about type.

CREATE TYPE KA_Type FROM INT

DECLARE @d KA_Type
SELECT @d = 2


SELECT @d