Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 5 February 2016

How to Create Computed Column in SQL Server

How to Create Computed Column in SQL Server

CREATE TABLE TestingComputedColumnPersisted
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL,
 TotalItems AS (ItemsInStore + ItemsInWarehouse) persisted
 )

select * from TestingComputedColumnPersisted

CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)


ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse


Insert into Inventory
select 6479,797,465





select * from Inventory



For such scenarios where calculated values are required or values are generated through manipulation on other columns, we have a powerful feature provided in SQL Server. This feature is "Computed Columns".
A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non computed column names or any combination of all these may be used along with operators to create a computed column. In this tip we will go through an example of implementing a computed column.
I have tested the scripts on SQL Server 2005 and SQL Server 2008. In the following script we will create a table called CCtest in the AdventureWorks database with three columns [empNumb], [DOBirth] , [DORetirement].
We are required to have the "Date of Retirement" for each employee as (DOBirth + 60 years - 1 day). Instead of calculating it each time in the report or updating the column [DORetirement] each time through a trigger when [DOBirth] is updated, we have a better approach here to create [DORetirement] as a computed column.  Since this rule could change at anytime we are implementing it as a computed column instead of a hard coded value.
Script # 1: Create a table with computed column
USE [AdventureWorks]
GO 
-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO
The same may be done through SSMS. Create new table by right clicking on the Tables folder in the AdventureWorks database.
You will be provided a design view for the new table in SSMS. Provide specifications for the calculated column as shown below.
Now we have our table CCtest with a computed column. Similarly we can add a computed column to any existing table using the "ALTER TABLE" command or opening the table in design view using SSMS and making the changes.
Let's insert some data and run a query to test the functionality of the computed column.
Script # 2: Insert data in table
USE AdventureWorks
GO 
INSERT INTO CCTest (empNumb, DOBirth)
SELECT 30 ,'1985-12-13' UNION ALL
SELECT 25 ,'1980-11-18' UNION ALL
SELECT 21 ,'1978-01-19' UNION ALL
SELECT 7 ,'1985-12-13' UNION ALL
SELECT 5 ,'1975-07-23' 
GO
SELECT * FROM dbo.CCTest
GO
Here we can see our computed column:
To verify that the computed column will be updated for any updates, we will update [DOBirth] for [empNumb] 25.
Script # 3: Update DOBirth of empNumb 25
USE AdventureWorks
GO 
UPDATE CCtest
SET DOBirth = '1960-03-25'
WHERE empnumb = 25
GO
SELECT * FROM dbo.CCTest
WHERE Empnumb = 25
GO
Here we can see our computed column has been updated.
PersistedYou may have noticed that we also used the property "Persisted" for our computed column. This property for computed columns has been introduced in SQL Server 2005 and onwards. It is important for any computed column, because many additional features depend on it. To be able to make a computed column as Persisted it has to be deterministic.
Here are a few rules:
  • If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.
  • Any update in referenced column will be synchronized automatically in computed column if it is Persisted.
  • Along with some other conditions Persisted is required to create an index on the computed column.
Nullability
Nullibility for a computed column value will be determined by the database engine itself. The result of a non-nullable referenced column may be NULL in certain conditions to avoid possible overflows or underflows. You can provide an alternate value for NULL using the ISNULL(check_expression, constant), if required.
Some Limitations
  • For SQL Server 2000 you can not create a persisted computed column.
  • You can not reference columns from other tables for a computed column expression directly.
  • You can not apply insert or update statements on computed columns.
  • If you are combining operators of two different data types in your expression then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.
  • A subquery can not be used as an expression for creating a computed column.
  • Computed columns can be used in SELECT lists, WHERE or ORDER BY clauses and as regular expressions , but to use a computed column as CHECK, FOREIGN KEY or NOT NULL constraints you have to set it to Persisted.
  • To use a computed column as Primary or Unique Key constraint it should be defined by a deterministic expression and data type of computed column expression should be indexable.

SQL SERVER 2008 Features of Introduction to Table-Valued Parameters

SQL SERVER 2008 Features of Introduction to Table Valued Parameters 


Ref:
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

select * from Contact

CREATE TYPE ContactType as Table(Email nvarchar(300),Firstname nvarchar(300),LastName nvarchar(300))

CREATE PROC INSERTCONTACT
@Ct_tvp ContactType Readonly
as
Insert into Contact(Email,FirstName,LastName)
select * from @Ct_tvp


Declare @Cttvp as  ContactType

Insert into @Cttvp(Email,FirstName,LastName)
values('pub@pubmail.com','pub','mail'),('ssf@wsfs.com','ssf','lrwr')

EXEC INSERTCONTACT @Cttvp

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:
  1. Create a table type and define the table structure
  2. Declare a stored procedure that has a parameter of table type.
  3. Declare a table type variable and reference the table type.
  4. Using the INSERT statement and occupy the variable.
  5. We can now pass the variable to the procedure.
For Example,
Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.
Department:
CREATE TABLE Department(DepartmentID INT PRIMARY KEY,DepartmentName VARCHAR(30)
)
GO
1. Create a TABLE TYPE and define the table structure:
CREATE TYPE DeptType AS TABLE(DeptId INTDeptName VARCHAR(30)
);
GO
1 DeptType SQL SERVER   2008   Introduction to Table Valued Parameters with Example
2. Declare a STORED PROCEDURE that has a parameter of table type:
CREATE PROCEDURE InsertDepartment@InsertDept_TVP DeptType READONLY
AS
INSERT INTO 
Department(DepartmentID,DepartmentName)SELECT FROM @InsertDept_TVP;
GO
Important points  to remember :
–  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
–  You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
2 Procedure SQL SERVER   2008   Introduction to Table Valued Parameters with Example
3. Declare a table type variable and reference the table type.
DECLARE @DepartmentTVP AS DeptType;
4. Using the INSERT statement and occupy the variable.
INSERT INTO @DepartmentTVP(DeptId,DeptName)VALUES (1,'Accounts'),
(
2,'Purchase'),
(
3,'Software'),
(
4,'Stores'),
(
5,'Maarketing');
5. We can now pass the variable to the procedure and Execute.
EXEC InsertDepartment @DepartmentTVP;GO

3 Execute SQL SERVER   2008   Introduction to Table Valued Parameters with Example
Let’s see if the Data are inserted in the Department Table
4 Select SQL SERVER   2008   Introduction to Table Valued Parameters with Example
Conclusion:
Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.
Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

User-Defined Table Type

When first taking a look at the new table-valued parameters, I thought that using this feature is a bit complicated. There are several steps involved. The first thing to do is to define a table type. If you look at the Types section under Programmability in the 2008 Management Studio, you will see the new “User-Defined Table Types” (Image 1).
Image 1
Image 1
At the time of this writing, RC0 is the latest release of SQL Server 2008, and the only way to create this new object is with a T-SQL script. I’m not sure it this will change with the released version, known as RTM, but it is not difficult to create the type. I found that there is a right-click option “New User-Defined Table Type…” that creates a template in the Query Window shown in Image 2.
Image 2
Image 2
By clicking the “Specify Values for Template Parameters” button, a dialog box pops up that will help complete the definition. Image 3 shows the dialog box after some of the values have been modified.
Image 3
Image 3
After filling in the appropriate values and clicking OK, a CREATE TYPE statement replaces the template. At this point, additional columns and constraints can be added before clicking OK. Here is the code that was generated:
-- ================================
-- Create User-defined Table Type
-- ================================
USE Test
GO

-- Create the data type
CREATE TYPE dbo.MyType AS TABLE 
(
 col1 int NOT NULL, 
 col2 varchar(20) NULL, 
 col3 datetime NULL, 
    PRIMARY KEY (col1)
)
GO

After running the code, the object definition is created and viewable in the “User-Defined Table Type” section (Image 4). You can view the properties there, but not modify them. To modify the type, you will have to drop it and then create it again with the modified definition.
Image 4
Image 4

Using the User-Defined Table Type

So far, it seems like we have done quite a bit of work, but we only have the type definition created. Like other programmable objects, it will stick around unless it is dropped. To use it in T-SQL code, you must create a variable of the new type and then populate it as you would any other table. Once it is populated, you can use it in other T-SQL statements. Because it is a variable, it goes out of scope automatically when the batch is completed. Notice in the code below that the name of the data type is the same as the type we just created.
DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
 (2,'def','1/1/2001'),
 (3,'ghi','1/1/2002'),
 (4,'jkl','1/1/2003'),
 (5,'mno','1/1/2004')
 
SELECT * FROM @MyTable 
As long as the variable does not go out of scope, you can do just about anything with it that you can do with a regular table variable, such as join another table or be used to populate another table. Like a table variable, you can not modify the table definition.
As I mentioned, the variable is gone once it goes out of scope. For example if you have a T-SQL script that is composed of more than one batch, the variable is only valid within the batch where it was created.

Using the Variable as a Parameter

So far, we haven’t seen anything that is not possible with a regular table variable. The benefit is being able to pass the variable of the new type to a stored procedure. A stored procedure must be created that uses the new type. Here is an example of that along with the code to create a regular table that we will be populating.

USE [Test]
GO

CREATE TABLE [dbo].[MyTable] (
 [col1] [int] NOT NULL PRIMARY KEY,
 [col2] [varchar](20) NULL,
 [col3] [datetime] NULL,
 [UserID] [varchar] (20) NOT NULL
 ) 

GO

CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
 @UserID varchar(20) AS
 
 INSERT INTO MyTable([col1],[col2],[col3],[UserID])
 SELECT [col1],[col2],[col3],@UserID 
 FROM @MyTableParam
 
GO
Notice the READONLY qualifier after the table parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the proc. Therefore, to eliminate changes to the variable inside the proc that would affect the original, no changes are allowed.
Finally, let’s put it all together and call the stored procedure. In the next code snippet, code from the previous section is used to create and populate the variable.
DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
 (2,'def','1/1/2001'),
 (3,'ghi','1/1/2002'),
 (4,'jkl','1/1/2003'),
 (5,'mno','1/1/2004')

EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'

SELECT * FROM MyTable

In order for a user to use the User-Defined Table Type, EXECUTE or CONTROL permission must be granted. This is the command to grant permission to a user:

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

Table-Valued Parameters are indeed a new feature in SQL Server 2008.  As the name implies, you can now pass a table type as a parameter to a function or stored procedure.  At a high level the TVP allows you to populate a table declared as a T-SQL variable, then pass that table as a parameter to a stored procedure or function.  The benefit of the TVP is that you can send multiple rows of data to the stored procedure or function rather than having to declare multiple parameters  or possibly use an XML parameter type to handle a variable number of rows.  According to Books on Line, a TVP is an efficient option for up to 1,000 or so rows.
In this tip we are going to gain an understanding of TVP by walking through a simple code sample to demonstrate how to:
  • Create a table type that can be passed as a TVP to a function or stored procedure
  • Create a stored procedure that uses a TVP
  • Declare the table type, populate it with data, and pass it to a stored procedure
Now let's describe our example.  In the extract, transform, and load (ETL) process in our data warehousing applications we typically map source system keys to surrogate keys during dimension processing; we then use the surrogate keys to uniquely identify the dimension rows in the warehouse.  This allows us to retain the complete history of dimension rows, as each change to a dimension row can be stored in a new row with a new surrogate key.  As dimension rows are changed or added, we simply assign a new surrogate key to the source system key and insert a new row into the dimension table.  For more details on surrogate key processing see our earlier tip Handling Slowly Changing Dimensions in SQL Server Integration Services (SSIS) Packages.  When processing fact rows we lookup the surrogate keys for the source system keys in the fact and store the surrogate key in the fact table; our queries join the fact table to the dimension table by the surrogate key.  Since multiple fact tables typically reference a given dimension (e.g. Customer), the  surrogate key lookup provides a good example for using TVP, allowing us to implement the surrogate key lookup one time in a stored procedure, then call it during our ETL process for multiple fact tables.
In addition to simply looking up the surrogate key for a source system key, we also have a situation where a fact table may have a source system key that doesn't exist in a dimension table.  In this case we want to create an inferred member in the dimension; i.e. create a new surrogate key and add it to the dimension then update it later when we get the actual dimension row from the source system.  For more details on inferred member processing for a dimension, take a look at our earlier tip Handling Early Arriving Facts in SQL Server Integration Services (SSIS) Packages.
The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 
Create a Table Type
In order to pass a table as parameter to a stored procedure or function, you first create a TABLE TYPE as follows:
CREATE TYPE SourceKeyList AS TABLE ( 
  SourceKey NVARCHAR(50)
)
GO
The T-SQL code is very similar to creating an ordinary table.  You can query sys.types in the current database to determine any table types that have been created:
SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1
Create a Stored Procedure with a TVP
We are going to create a stored procedure that performs the surrogate key lookup and adds an inferred member if the source key doesn't exist.  First we need to create a sample dimension table:
CREATE TABLE dbo.dim_Customer (
 sk_Customer  INT IDENTITY NOT NULL,
 CustomerSourceKey NVARCHAR(50) NOT NULL,
 CustomerName  NVARCHAR(50) NOT NULL,
 InferredMember  BIT NOT NULL
)
The surrogate key is an integer type and we use the IDENTITY property to automatically assign the next sequential number when inserting rows.  The InferredMember column gets set to 1 when we insert a row for a source key that doesn't exist.  When the row is extracted from the source system during dimension processing, the inferred row is updated with the columns from the source and the InferredMember column is set to 0.
Now let's create a stored procedure that takes our table type as a parameter and performs the surrogate key lookup and inferred processing:
CREATE PROCEDURE dbo.stp_GetCustomerSK
@source_key_list SourceKeyList READONLY
AS
BEGIN
 INSERT INTO dbo.dim_Customer(
 CustomerSourceKey, CustomerName, InferredMember
 )
 SELECT SourceKey, N'INFERRED', 1
 FROM @source_key_list k
 LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey
 WHERE sk_Customer IS NULL
 
 SELECT sk_Customer, CustomerSourceKey
 FROM dbo.dim_Customer c
 JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey
END
GO
The TVP must be declared READONLY.  You cannot perform any DML (i.e. INSERT, UPDATE, DELETE) against the TVP;  you can only reference it in a SELECT statement.  The stored procedure joins the TVP to the customer dimension to determine any source keys that do not already exist then inserts them.  The stored procedure then joins the TVP to the customer dimension to return a result set of the source key and their corresponding surrogate key.
You can query sys.parameters to view any parameters that have been declared READONLY:
SELECT object_id, name FROM sys.parameters
WHERE is_readonly = 1
GO
Declare TVP, Populate and Pass as a Parameter
You declare a T-SQL variable as a table type then populate it with rows by using an INSERT statement:
DECLARE @source_key_list SourceKeyList

INSERT INTO @source_key_list
SELECT 'CustomerID_001' UNION ALL
SELECT 'CustomerID_002' UNION ALL
SELECT 'CustomerID_003'

EXEC dbo.stp_GetCustomerSK @source_key_list
GO
For demonstration purposes the above SELECT statement just hard-codes values to insert for the source system keys; you would normally do a SELECT DISTINCT from your source system table to extract the list of source system keys on which you want to perform the surrogate key lookup.  The output from the above would look like this:
The output shows the surrogate key for each source key.

Wednesday, 3 February 2016

Login Failed for User Cannot open user Default Database. Login Failed(Microsoft SQL Server, Error:18456) on Linked Server


Note:

kindly create linked server with SQL Authentication with sysadmin it should exists on both server as same sysadmin credentials

Step 1:

EXEC sp_addlinkedserver
   @server=N'LinkedServerName',             -- Remote Computer Name linked server name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'SQLEXPRESS2008'; --Machine Server Name \Instance Name


EXEC sp_addlinkedserver
   @server=N'LinkedServerName',             -- Remote Computer Name linked server name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'WELCOME-PC\SQLEXPRESS2008'; --if instance name is null then machine name enough


Step 2:

EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'true';

 ----to solve logine failed for user before user muste be existed on both source and destination with sysadmin login----

Step 3:

 EXEC sp_addlinkedsrvlogin       @rmtsrvname='LinkedServerName',
                                 @useself=N'False',
                                 @locallogin=NULL,
                                 @rmtuser='LinkedServerLogin',
                                 @rmtpassword='password'

Step 4:

SELECT * FROM linkedservername.databasename.dbo.tablename

Ex:-

Select * from TestLinkedServer2008.DMVTest.dbo.t1

Step 5:

Exec sp_readerrorlog---------------to know error state


Demo:

Login name 'VerifyLogin' have public privilges not have sysadmin it did nt exists on

Destination(SQL2005 LinkedServer Name). When you Right Click of SQL2005 LinkedServerName

and Click Test Linked Serer Connection then you will get Error as see below





When you tested on Source Server you know it have public login 

It is due to login have doesn't have exist on Destionation(SQL2005) Server.

So After Create it Login on Destination  Server with Public or any privilges it worked correctly.




Tuesday, 19 January 2016

SP_MSforeachDB using Filter To get all object who is having text inside objects

SQL Server: Applying Filter on sp_MSforeachDB


Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.
Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

You can even use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
       WHERE name  LIKE ''?%'' -- Only Files starting with DB name
END'

Output can be saved in tables (user, temporary) or table variables
DECLARE   @DatabasesSize TABLE
    (
      name VARCHAR(50),
      physical_name VARCHAR(500),
      state BIT,
      size INT
    )

INSERT  INTO @DatabasesSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


---------------To get all object who is having text inside objects---------------
declare @SQL nvarchar(max)
 
set @SQL = ''

select @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
SELECT ' +quotename([name],'''') + 'as [Database Name], so.name AS [Table Name],so.xtype
FROM syscomments c   
    join sysobjects AS so on c.id = so.id   
WHERE c.TEXT LIKE ''%test%''   ---------text to find inside code
    ' from sys.databases  
 
execute (@SQL)