SQL SERVER 2005 SYNONYMS - Usage And Limitations

SYNONYMS:

Problem:

At work we face situations like ,

1. where tables created in the production database are required to move to another DB as a minimal time period, Moving the table with data's is a tedious job

2. And also we use various SPs, TVF Functions in some DB say DB1. When we are working in some other DB2 and we have situation to use the same SPs and Functions in DB1 for a test process, here we need to run the whole scripts in DB2 and we have to drop it if it is no more useful which comsume time

Solution:

The feature is SYNONYMs in SQL Server 2005. SYNONYMs is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object(May be a Table,SP,Functions,Views etc). It is just a pointer or reference, so it is not considered to be an object.

Required Permissions:

In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..

CREATING SYNONYMs:

A SYNONYM can be created within a DB or Between DBs and also Between DBs in Different Servers.

Example 1:

Here is an Example for Creating a Synonyms within a DB

Syntax:

CREATE SYNONYM [SynName] FOR [ObjectName]

USE AdventureWorks

GO

CREATE SYNONYM MySyn FOR Production.Location

GO
To check that this works you can issue a query like

SELECT * FROM MySyn

This returns the values of the table Production.Location . This any modifications to this Synonyms will reflect in Production.Location table.

Say for example

UPDATE MySyn

SET Name = 'MukundsIdeas'

WHERE LocationID = 1

Executing this query will affect the values in the
Production.Location table of AdventureWorks DB.

Example 2:

Here is an example to create the SYNONYM in one database that references an object in another database.

USE master

GO

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Location

GO

This creates a Synonym that can be accessible form any DB inside the Same Server.

Note: Make note on what Schema your DB use. For eg : if ur DB uses schema dbo then u should mention it as

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.dbo.Production.Location

Example 3:

USE master
GO
CREATE SYNONYM dbo.MySyn FOR [UrServerName].[DBName].[TableName or SPname or Functionname or viewname etc]
GO

To get the meta data for all synonyms use the following command

SELECT * FROM sysobjects WHERE xtype = 'SN' ORDER BY NAME

And to drop the synonym use the following command

USE AdventureWorks;

GO

DROP SYNONYM MySyn

GO

SYNONYM's can be very useful and can be created for

1. Tables
2. Views
3. Assembly Stored Procedures,

4. Table Valued Functions,

5. Aggregations
6. SQL Scalar Functions
7. SQL Stored Procedures
8. SQL Table Valued Functions
9. SQL Inline-Table-Valued Functions
10. Local and Global Temporary Tables
11. Replication-filter-procedures
12. Extended Stored Procedures

Benefits :

SYNONYMs provide a layer of abstraction over the referenced object
Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.

Provides flexibility for changing the location of objects without changing existing code.

SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.

SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations:

SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.

Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.

Obviously consumes possible object names, as you can not create a table with the same name of a synonym

The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.

SYNONYM can not be referenced in a DDL statement

User Defined SPLIT Functions in SQL SERVER 2005

User Defined Function for Splitting the string value using the delimiter:
This is a function that requires the string value and the delimiter as input and returns a column of splitted values

CREATE FUNCTION [dbo].[Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([ID] BIGINT identity(1,1), [Value] nvarchar(4000))

BEGIN
DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)

--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)

--Check for trailing Delimiter, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter

--Get position of first Delimiter
SET @Pos = charindex(@Delimiter,@String) SET @NextPos = 1

--Loop while there is still a Delimiter in the String of levels
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT INTO @ValueTable ( [Value]) Values (@NextString)
SET @String = substring(@String,@pos +1,len(@String))
SET @NextPos = @Pos
SET @pos = charindex(@Delimiter,@String)
END
RETURN
END

For eg Executing the Query
SELECT [Value] FROM [dbo].[split]('1,2,3',',') will result in
table
Value
1
2
3
This function is useful when we pass a string list to a SP.

Mathematical Calculations in SQL server

Calculating Mathematical Values in SQL Server :

Problem:

In our application we have the need to perform mathematical calculations.Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations.

Does SQL Server perform basic mathematical calculations?
Solution: Yes - SQL Server can perform basic addition, subtraction, multiplication and division. So if you need some of those basic building blocks those are available and we will walk through some examples in this tip.

In addition, SQL Server can calculate SUM, COUNT, AVG, etc. For these type of calculations, check out SQL Server T-SQL Aggregate Functions.

To address the multiple code issues, I would recommend researching stored procedures. This tip Getting started with SQL Server stored procedures is probably a good place to start.In this tip, let's focus on some basic building blocks to perform your calculations with the T-SQL language.

Here are the examples we will cover:
• Calculations on Values

• Calculations on Parameters

• Calculations on Columns

• Calculations in Computed Columns

Calculations on Values:
As a starting point, values themselves can have mathematical operations performed on them. So in the code below we are performing addition (+), subtraction (-), multiplication (*), division (/) and a combination of operations.

In these examples, both positive and negative whole numbers and decimals are used. The value calculated by the statement is below the code to serve as a point of reference. Review and copy the code into a SQL Server 2005 Management Studio window to get a feel for these calculations. Certainly this list is not comprehensive, so feel free to test some operations in your applications or from your experiences.

Calculations on Values:
-- Example 1 - Addition (+) :

SELECT 1 + 1

-- = 2

SELECT 1 + 2 + 3 + 4 + 99 + 704

-- = 813

SELECT 1.5 + 1.5 -- = 3.0

SELECT .25678 + .00096356

-- = 0.25774356

SELECT 1.75 + -2.25

-- = -0.50

Example 2 - Subtraction (-):

SELECT 1 - 1-- = 0

SELECT 918 - 704-- = 214

SELECT 3.2 - 1.9-- = 1.3

SELECT 1.9 - 3.2-- = -1.3

SELECT 9 - 3 - 3 -- = 3

SELECT .75 - .68-- = 0.07--

Example 3 - Multiplication (*):

SELECT 1 * 1-- = 1

SELECT 2 * -4-- = -8

SELECT 2 * 5 * 10-- = 100

SELECT 1.25 * 3-- = 3.75

SELECT .4 * .5-- = .20--

Example 4 - Division (/):

SELECT 1/2-- = 0

SELECT 1.0/2.0-- = 0.500000

SELECT 0/5-- = 0

SELECT 100/12-- = 8

SELECT 100.0/12.0-- = 8.333333

SELECT -75.0/4.5-- = -16.666666

SELECT .5/.1-- = 5.000000-- Example 5 - Combination

SELECT ((100 + 100) * .05)-- = 10.00

SELECT (10 - 5)/2-- = 2

SELECT (10.0 - 5.0)/2.0-- = 2.500000

SELECT ((100 + 100) - (50 + 50))-- = 100

Calculations on Parameters:
Since we have covered the bulk of the calculations in the section above let's just focus on a few different operations to show how parameters with specific data types play an important role. Since this set of examples are not comprehensive, feel free to copy the code below into a SQL Server 2005 Management Studio window and test the code with some of your own logic.
Calculations on Parameters
-- Variable declaration

DECLARE @i1 int

DECLARE @i2 int

DECLARE @i3 int

DECLARE @d1 decimal(10,2)

DECLARE @d2 decimal(10,2)

DECLARE @d3 decimal(10,2)

-- Initialize variables

SET @i1 = 100

SET @i2 = 75

SET @i3 = 50

SET @d1 = 1.5

SET @d2 = 5.5

SET @d3 = .575--

Example 1 - Addition (+)

SELECT @i1 + @i2 + @i3 + @d1 + @d2 + @d3 -- = 232.58

SELECT @d2 +

-@d3-- = 4.92

-- Example 2 - Subtraction (-):

SELECT @i2 - @i3

-- = 25

SELECT @d2 - @d3

-- = 4.92

-- Example 3 - Multiplication (*):

SELECT @i2 * @i3

-- = 3750

SELECT @d2 * @d3

-- = 3.1900

-- Example 4 - Division (/):

SELECT @i2 / @i3

-- = 1

SELECT @d2 / @d3

-- = 9.4827586206896

-- Example 5 - Combination:

SELECT ((@i1 + @i2) * @d2)

-- = 962.50

SELECT ((@i1 + @i2) - (@d1 + @d2))

-- = 168.00

Calculations on Columns:

Another option is to calculate the values based on a SELECT statement as the example below shows. If the data is in 1 or more columns, this approach eliminates the need to calculate the values based on additional parameters or logic.
Calculations on Columns
-- Sample Table

CREATE TABLE dbo.CalculationExample

(

ProductID int NOT NULL,

Cost decimal(10,2) NOT NULL

)

GO
-- Populate Table

INSERT INTO dbo.CalculationExample (ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00UNION SELECT 3, 25.00

GO
-- Verify Insertion

SELECT * FROM dbo.CalculationExample

GO
-- Declare Variables

DECLARE @MarginPercent decimal(10, 2)

DECLARE @TaxPercent decimal(10, 2)

-- Initialize Variables

SET @MarginPercent = .20

SET @TaxPercent = .05

-- Calculate Values

SELECT ProductID, Cost, Cost * @MarginPercent AS 'Margin',

Cost * @TaxPercent AS 'Tax',

Cost + (Cost * @MarginPercent) + (Cost * @TaxPercent) AS 'FinalCost'

FROM dbo.CalculationExample

GO

Calculations in Computed Columns:

Let's take those same calculations and now move them to computed columns in the table. Here is that example:

Calculations in Computed Columns

CREATE TABLE [dbo].[CalculationExample_ComputedColumns]

([ProductID] [int] NOT NULL,

[Cost] [decimal](18, 0) NOT NULL,

[Tax] AS ([Cost]*(0.05)),

[Margin] AS ([Cost]*(0.20)),

[FinalCost] AS (([Cost]+[Cost]*(0.20))+[Cost]*(0.05)),

CONSTRAINT [PK_CalculationExample_ComputedColumns] PRIMARY KEY CLUSTERED ([ProductID] ASC)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

-- Populate Table

INSERT INTO dbo.CalculationExample_ComputedColumns

(ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00

UNION

SELECT 3, 25.00

GO
-- Review the values

SELECT * FROM dbo.CalculationExample_ComputedColumnsGO

Ref: http://www.mssqltips.com/tip.asp?tip=1547