The Evolving System Tables from SQL Server 2000 to 2012

Error Handling in SQL Server 2000 - 2012

The Differences between functions and stored procedures

	Functions (select data only)	SPs (can modify data)
Purpose	For returning a computation value used in other T-SQL statements	For performing business logic with a set of complex T-SQL statements, or interacting with outside world.
Which T-SQL elements can be used inside?	SELECT only (cannot modify the data or objects)	DML, DDL, Transaction, Error Handling
How to be used?	SELECT, WHERE, HAVING EXEC (for deterministic functions)	EXEC
Deferred Name Resolution?	No	Yes
Need input parameters?	Optional (0 ~ 1023)	Optional (0 ~ 21000)
Which SQL Server data types can be used as input parameters?	All including table variable but not text, ntext, image, timestamps	Any SQL Server data types including table variable (companying with READONLY).
Have to return a data?	Yes (a single value or a table)	Optional (0 ~ Many) – using OUTPUT or OUT
Which SQL Server data types can be used as output parameters?	All including table variable but not text, ntext, image, timestamps	Any SQL Server data types, but not a table variable. Cursor is used as output only accompanied by the VARYING keyword.
Return a status code?	No	Yes
Can a temp table be used inside?	No (but table variable is OK)	Yes (table variable OK too)
Can call another function	Yes	Yes
Can call another SP?	No (but can call an extended stored procedure.)	Yes
Can be used for a dynamic SQL?	No	Yes
Can a non-deterministic function be used?	No	Yes
Typical use cases	SELECT – inline function WHERE HAVING	SQL Codes in the applications vs. Stored Procedure in SQL Server? The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include: They allow modular programming. They allow faster execution.  They can reduce network traffic. They can be used as a security mechanism.  Protect against SQL injection attacks. When to use SPs? – if the codes need to be reused If the operation requires a large amount of T-SQL code or is performed repetitively, If an operation requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. If you want to grant users permission to execute a stored procedure but not permission to execute the procedure's statements directly. In dynamic SQL,if you want to pass data values to a backend stored procedure. When to place SQL directly in your application? – If the codes are dynamic When the SQL is generated on the fly. That is, you cannot create and cache an execution plan since it's different each time it's called.  Or the code is executed will depend on your environment (i.e., the server). Its dynamic nature negates many of the advantages of a stored procedure.  In dynamic SQL，if you want to pass data values to the application code to construct the T-SQL string.
Caveat	Use other options instead of UDF if it makes the query non-sargable or cursor-type. Create the function with SchemaBinding	Include SET NOCOUNT ON statement  Use schema name with object name Generally do not use the prefix “sp_” in the stored procedure name Use IF EXISTS (SELECT 1) instead of (SELECT *) Use the sp_executesql stored procedure instead of the EXECUTE (@AString) Try to avoid using SQL Server cursors whenever possible  Keep the Transaction as short as possible Use TRY-Catch for error handling

How to find the owner of an object and how to change the owner in SQL Server?

In SQL Server 2000, users own object. In SQL Server 2005 and onward, users do not own objects, instead, they own schema. Thus, by default, the owner of an object should be the owner of its schema. But you can change it if necessary.

1. How to find the owner of an object

SELECT o.object_id,

   o.type,

   o.name,

   CASE

     WHEN principal_id IS NOT NULL

                  THEN (SELECT name

                  FROM   sys.database_principals dp

                  WHERE  dp.principal_id = o.principal_id)

     ELSE (SELECT dp.name

              FROM   sys.database_principals dp,

                  sys.schemas s

              WHERE  s.schema_id = o.schema_id

                  AND s.principal_id = dp.principal_id)

   END AS Owner

FROM   sys.objects o

--WHERE type='U'

ORDER  BY o.name

GO

(http://www.sqlservercentral.com/Forums/Topic324773-149-1.aspx)

Or more concisely as below:

SELECT 

    so.[name] AS [Object Name]

  , sch.[name] AS [Schema Name]

  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner]

  , type_desc AS [ObjectType]

FROM sys.objects so

  JOIN sys.schemas sch

    ON so.[schema_id] = sch.[schema_id]

     order by so.name

http://www.mssqltips.com/sqlservertip/1778/ownership-chaining-in-sql-server-security-feature-or-security-risk/

2.   How to change the object owner?

SQL Server 2000 (use sp_changeobjectowner)

SQL Server 2005 and Up (use ALTER SCHEMA and ALTER AUTHORIZATION)

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner' -- Changes the owner of the authors table to Corporate\GeorgeW. EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'

ALTER SCHEMA (Transact-SQL) Transfers a securable between schemas. /*The following example modifies the schema HumanResources by transferring the table Address from schema Person into the schema. */ USE AdventureWorks2012; GO ALTER SCHEMA HumanResources TRANSFER Person.Address; GO ALTER AUTHORIZATION (Transact-SQL) Changes the ownership of a securable. /*A. Transfer ownership of a table The following example transfers ownership of table Sprockets to user MichikoOsada. The table is located inside schema Parts. */ ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada; GO --The query could also look like the following: ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada; GO /*B. Transfer ownership of a view to the schema owner The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. The view is located inside schema Production. */ ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER; GO /*C. Transfer ownership of a schema to a user The following example transfers ownership of the schema SeattleProduction11 to user SandraAlayo. */ ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo; GO /*D. Transfer ownership of an endpoint to a SQL Server login The following example transfers ownership of endpoint CantabSalesServer1 to JaePak. Because the endpoint is a server-level securable, the endpoint can only be transferred to a server-level principal. */ ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak; GO