Magic Table and Temporary stored procedures

• Magic Table 

                Inserted and deleted are called magic tables (only internal tables no physical tables ).

        

Use -         Magic tables are used to put all the deleted and updated rows .

                   We can retrieve the column values from the deleted rows using the keyword “deleted” /inserted 

How to get data from-  Using  OUTPUT Clause

• Temporary stored procedures

Temporary stored procedures on Microsoft SQL Server are prefixed with a pound sign #. One pound sign means that its temporary within the session, two pound signs ## means its a global temporary procedure, which can be called by any connection to the SQL server during its lifetime.

Reason to use -  Using for advantage of proc in absence of having create object permission on any data base 

Example  SSIS   

     The user  is not having create object permission on the data base and have to update / insert or get data from database using (based on parameter ) Execute sql  task.

  

1 Crete Dynamic SQL ( using veritable  or using parameter )


    Disadvantage  Every time sql statement will be validated compiled and plan will be created (performance hit ) .


2 .Call Sp (temp) with parameter 


    Advantage - Performance gain due to every time recompilation of sql statement and availability of Cashed plan

• Index

==============

1 If any index is created over any column then column data type can not altered 

Example 

Run following script .



CREATE TABLE [dbo].[test]
(
[Name] [nchar](10) NOT NULL,
[id] [int] NULL
) ON [PRIMARY]


GO
-----------------
ALTER TABLE [dbo].[test] ALTER COLUMN [Name] [nchar](20) 


-- will execute
GO


CREATE UNIQUE CLUSTERED INDEX [Index_Name ] ON [dbo].[test] 
(
[Name]  ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


 -------------------------
ALTER TABLE [dbo].[test] ALTER COLUMN [Name] [nchar](10) 
------------------------------ it fails  


--- Eror Message 


-- Msg 5074, Level 16, State 1, Line 2
--                  The index 'Index_Name ' is dependent on column 'Name'.
--Msg 4922, Level 16, State 9, Line 2
--            ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.

• Implicit data conversion in sql 

Lower Precedence data type is automaticly converted in higer Precedence

Data Type Precedence in Sqlservere

user-defined data types (highest)
sql_varian t
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)