Archive for February, 2010

Understanding sys.objects:

Sys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.

Using Sys.objects returns list of all database objects and its types, type can be either of given below:








V     VIEW

How to DELETE all User Tables , stored procedures , UDF’s and Views using cursor

Use [database name]

declare @q nvarchar(max)

declare @name nvarchar(max);

declare @type nvarchar(max);

declare cur cursor for

select name ,type from sys.objects where type in(‘p’,‘fn’,‘v’,‘u’);

open cur;

fetch next from cur into @name,@type

while @@fetch_status = 0




set @q=N‘drop procedure ‘ + @name;




set @q=N‘drop function ‘ + @name;




set @q=N‘drop view ‘ + @name;




set @q=N‘drop table ‘ + @name;


exec sp_executesql @q;

fetch next from cur into @name,@type


close cur;

deallocate cur;


Read Full Post »

Encapsulation: is an ability of hiding data or methods from the rest of the world.

Inheritance: is a concept of passing and using attributes of base class into derived class.

Polymorphism: single name – multiple use, it can be achieved via Function overloading & operator overloading.

Class & object: a class is a definition which describes all attributes of entity or an object. And object is an instance of a class.

Overloading: is the concept of using function or class with same name but different implementation by changing types of parameters

Static or shared: a keyword to define static class, members of static class doesn’t require creating instance of that class.

Virtual: indicated that this base class method is to be overridden. [Without specifying virtual keyword also method can be override, but it makes code more understandable]

Sealed: indicates that this base class method not to be override.

Overriding: is a concept of implementing a method in derived class with same definition of the base class method

Shadowing: without overriding a method derived class can do new implementation where parameters, return type & access modifier may differ from the base class implementation, Shadow is the keyword in C#

Constructor: is a method with same name as class, it gives a way to initiate the class members to default values at the time of object creation, automatically calls whenever class object is created

Static constructor: a type of constructor, calls at the first time object creation.

Serialization: is the process of converting object into a stream of bytes. De-serialization is the reverse process.

Delegate: holds a reference to a function. Type safe pointer

Multicast delegate: holds reference to multiple functions, its return type must be void.

Interface & Abstract:

Abstract class Interface
Is a class to provide common fields/members to subclasses Is a collection of member definitions to be implemented in derived class
a class can inherit only 1 abstract class A class can implement many interface
Members of abstract class can have any access modifier All members are only public by default, it can’t be changed
Abstract class method may or may not have implementation Interface can have only definition of methods, no implementation

Read Full Post »

Normalization: is a process of organizing data and minimizing redundancy

De-normalization: is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Stored Procedure: is a named group of T-SQL statements which can be created and stored in Database as an object.

Primary Key: is a unique identifier of a row in a DB table, [it can’t be NULL]

Unique key: forces uniqueness to a respective table column, [it can be NULL]

Foreign Key: a foreign key in 1 table refers to the primary key in other table, Used to force referential integrity.

Inner join: exists in both tables

Left Outer join: all records from left side table + matched rows from right side table (totals number of rows will be same as left table)

Right Outer join: all records from right side table + matched rows from left side table (totals number of rows will be same as right table), it’s a mirror image of left outer join

Full Outer join: all records from left side table + all records from right side table, weather matched or not

Cross join: returns [left table rows * right table rows], a Cartesian product of both tables

Self join: when table joins to itself using diff aliases to avoid confusion

Union: selects only distinct records from both tables

Union all: selects all records from both tables

View: is a subset of a table, can be used to retrieve data, insert or Update data. Can contain multiple select statements inside

Trigger: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

Cursor: is a database object used to loop trough records on row by row bases.

Index: pointers to data records, represents structure of how data get stored physically in a table

Clustered index Non clustered index
Reorders physical data stored in table It contains pointers to data rows
A table can have Only 1 clustered index A table can have one OR many non-clustered index
Table is having By default a clustered index
Leaf nodes contains data Leaf nodes contains reference to data

Linked server: is a concept of adding other remote server to a group to query DB’s of both servers together

Collation: set of rules that determines how data stores & compares in database

Collation types: case sensitive, accent sensitive, kana sensitive, width sensitive

Data ware housing:

  1. Record should Never delete from DB
  2. All records must be linked
  3. Once committed records should be read-only
  4. All changes made must be tracked with time

User defined function (UDF): is a bunch of T-SQL statements which accepts 0 or more parameters and returns a scalar data value or table.

DDL: data definition language – e.g. TRUNCATE command is a DDL command

DML: data manipulation language – e.g. INSERT, UPDATE & DELETE are DML commands

Read Full Post »

%d bloggers like this: