Category Archives: Database

How to Create Alias in SQL Server?

Leave a reply

What is Alias?
A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

You can create Alias from Configuration Manager.

Go to SQL Server Configuration manager – Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.

Alias Name — Alernative name of SQL Server
Port No — Specify the Port No
Server – Mentioned the Server Name or IP address

Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.

Identify Objects Type using sys.SysObjects

1 Reply

Today I received an email from my one of blog reader regarding identification of different objects using Sys.sysObjects.

You can query the sys.SysObjects to get all the objects created with in the database.
Example

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = ‘u’ 

Below are the different kind of object you can retrieve from database.

Object Type Abbreviation
AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Linked Server in SQL Server

Leave a reply

A linked server configuration enables SQL Server to execute commands/T-SQL statements against OLE DB data sources on remote servers. You can query heterogeneous databases using linked server.

Advantage of Linked Server
Remote server access
You can execute distributed queries, updates, commands, and transactions on heterogeneous data sources using linked server
The ability to address diverse data sources similarly.


-- Below procedure will create linked server to communicate with access

Sp_addlinkedserver ‘Lnk_AccessDB’, ‘Access’, ‘Microsoft.Jet.OLEDB.4.0’,

‘c:\db1.mdb’

-- query the linked server

SELECT *

FROM   lnk_accessdb...tb1

-- Below query will drop the linked server

EXEC Sp_dropserver ‘Lnk_AccessDB’ 

GO Keyword with Integer Parameter

Leave a reply

In SQL Server the keyword GO tells SQL Server to execute the preceding code as one batch. From SQL Server 2005 onwards we can specify the integer parameter with GO keyword which will loop the preceding statement.

Example



create table jugal

(

name varchar(100) default ‘jugal shah’

)

— below command will insert the default value two times as we have specified 2 as parameter

insert into jugal values(default)

go 2


— below command will print ‘Jugal Shah’ 100 times as we have specified 100 as parameter

print ‘jugal shah’

go 100