Permissions

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

List counts of all tables in DB

select o.name as "Table Name",i.rowcnt as "Row Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
order by o.name

composable DML for Auditing

You can achieve this by using composable DML. You write an UPDATE statement with an OUTPUT clause, and define a derived table based on the UPDATE statement. You write an INSERT SELECT statement that queries the derived table, filtering only the subset of rows that is needed. Here's the complete solution code:
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE SupplierID = 1) AS D
WHERE oldval <>= 20.0;

Clientaccesspolicy.xml

Clientaccesspolicy.xml

can be used with Blend & Silverlight !!!

SET

Category
Statements
Date and time statements
SET DATEFIRST
SET DATEFORMAT
Locking statements
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
Miscellaneous statements
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET FIPS_FLAGGER
SET IDENTITY_INSERT
SET LANGUAGE
SET OFFSETS
SET QUOTED_IDENTIFIER
Query Execution Statements
SET ARITHABORT
SET ARITHIGNORE
SET FMTONLY
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
ISO Settings statements
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
Statistics statements
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
Transactions statements
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT

Resource Database in SQL server 2005/2008

Resource Database in SQL server 2005/2008

Helpful for service pack installation for DBA

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata


SELECT SERVERPROPERTY('ResourceVersion')
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

DW 2.0

DW 2.0 offers some major signifi cant improvements over fi rstgeneration data warehouses.

DW 2.0 offers the recognition that there
is a life cycle of data once it enters the data warehouse, the notion
that structured and unstructured data belong in the data warehouse,
and the idea that metadata is an essential and normal part of the data
warehouse.

Four Sectors are

1. Interactive
2. Integrated
3. Near Line
4. Archival

SQL Server 2008 T-SQL enhanceMents

SQL Server 2008 T-SQL enhanceMents
-----------------------------------------------

1. Initialize the varaible inline as part of the variable declaration statement instead of using separate
DECLARE and SET statements
2. Compound Assignment Operators.

3. Table Value Constructor Support through the Values clause. Use a single VALUES clause to construct a set of rows.
A table value constructor can be used to define table expressions such as key derived tables and CTEs, and can be used where

table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement).

4. Enhanced CONVERT function with Style 0 (default) , Style 1 and Style 2

eg :

SELECT

CONVERT(VARCHAR(12) , 0x49747A696B , 2) ,

CONVERT(VARBINARY(5), '49747A696B' , 2);

This code produces the following output:

49747A696B 0x49747A696B

5. New Date and Time Data Types

4 New data types - DATE, TIME, DATETIME2, and DATETIMEOFFSET
new and Enhanced Functions - SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET

6. Large UDTS ( User Defined Types) case of spatial data
In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes, but now 2 GB

7. HIERARCHYID Data Type
The new HIERARCHYID data type in SQL Server 2008 is a system-supplied CLR UDT that can be useful for storing and manipulating

hierarchies

8. Table Types

Table types enable you to save a table definition in the database and use it later to define table variables and parameters to

stored procedures and functions. Because table types let you reuse a table definition, they ensure consistency and reduce

chances for errors

To get metadata information about table types in the database, query the view sys.table_types.

9. Table-Valued Parameters
you cannot set variables and parameters of a table type to NULL.
able-valued parameters are treated internally like table variables. Their scope is the batch (procedure, function). They have several

advantages in some cases over temporary tables and other alternative methods:
· They are strongly typed.
· SQL Server does not maintain distribution statistics (histograms) for them; therefore, they do not cause recompilations.
· They are not affected by a transaction rollback.
· They provide a simple programming model

10. MERGE Statement

The new MERGE statement is a standard statement that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on

conditional logic

11. GROUPING SETS, CUBE, and ROLLUP Subclauses

eg :
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty

FROM dbo.Orders

GROUP BY GROUPING SETS (
( custid, empid, YEAR(orderdate) ),
( custid, YEAR(orderdate) ),
( empid, YEAR(orderdate) ),
() );

CUBE( a, b, c ) is logically equivalent to: GROUPING SETS((a),(b),(c),(a, b),(a, c),(b, c),(a, b, c),())
For n elements. CUBE produces 2^n grouping sets.


ROLLUP( country, region, city )

is logically equivalent to:

GROUPING SETS((country, region, city),(country, region),(country),())

Notice that cases that have no business value, assuming a hierarchy between the elements—such as (city)—were not produced. There might

be multiple cities with the same name in the world, and even within the same country, so there is no business value in aggregating

them.


12. Grouping Sets Algebra

The following code represents a Cartesian product between two GROUPING SETS subclauses:

GROUPING SETS ( (a, b), (c, d) ), GROUPING SETS ( (w, x), (y, z) )

This code is logically equivalent to:

GROUPING SETS ( (a, b, w, x), (a, b, y, z), (c, d, w, x), (c, d, y, z) )

13. GROUPING_ID Function
The GROUPING_ID function lets you identify the grouping set that each result row belongs to

14. Sparse Columns

Sparse columns are columns that are optimized for the storage of NULLs. To define a column as sparse, specify the SPARSE

attribute as part of the column definition. Sparse columns consume no storage for NULLs, even with fixed-length types; however, when a

column is marked as sparse, storage of non-NULL values becomes more expensive than usual.

15. Filtered Indexes and Statistics


SQL Server 2008 introduces filtered indexes and statistics. You can now create a nonclustered index based on a predicate, and

only the subset of rows for which the predicate holds true are stored in the index B-Tree. Similarly, you can manually create

statistics based on a predicate. The optimizer has the logic to figure out when such filtered indexes and statistics are

useful.Well-designed filtered indexes can improve query performance and plan quality because they are smaller than nonfiltered

indexes. Also, filtered statistics—whether created automatically for a filtered index or manually—are more accurate than

nonfiltered statistics because they need to cover only a subset of rows from the table.

MS Dev Virtual Class.................

http://www.msdev.com/directory/SeriesDescription.aspx?CourseId=20


more from BI>>>>>>>>>>>>>>>>>


http://www.msdev.com/Directory/SearchResults.aspx?productId=15


Virtual Labs.. More @ http://msdn.microsoft.com/en-us/cc138238.aspx