Most of the times for DW loading purposes when you get technical specifications sheet from BA, it will include the tables meta-data. From the spreadsheet, you can use excel concatenate to build a SQL script for the DDL statements.

Here is sample file with formulas sourced from mssqltips.com TableBuilder

1. search for computer management
2. navigate to local users and groups
3. modify the group SQLServerMSASUser$ComputerName$MSSQLSERVER
4. Then add login

Default Instance – Machine name or server name                                                                                      Named instance – servername$instancename : WIN2008$EARTH

SQL Server can have 1 default and 32 named instances.

We have specific setting in SQL Server using which we can specify the use of CPUs. Default configuration is to use all the available CPUs and queries are executed in parallel. But the requirements vary depending on OLTP or OLAP systems.

MAXDOP : Maximum degree of Parallelism.

The values of all SQL Server related configuration properties can be seen from the query

[cc lang=”SQL”]

select * from sys.configurations

— How to use MAXDOP?

SELECT * from dbo.Authors
OPTION (MAXDOP 1)

[/cc]

A good amount of testing is needed after the configuration changes.

How to encrypt a SP?

Using WITH ENCRYPTION keyword as below:

[cc lang="SQL"]

CREATE PROCEDURE uspPersons WITH ENCRYPTION
AS
SELECT * FROM person.contact
GO

[/cc]

If we try to view using the system stored procedure like sp_helptext it will shout “objects have been encrypted”.

How to decrypt?

Alter the proc without the “WITH ENCRYPTION” key word.

[cc lang="SQL"]

CREATE PROCEDURE uspPersons
AS
SELECT * FROM person.contact
GO

[/cc]