NULL Operations in SQL Server

 

 

Using Coalesce()

When we have multi-value attribute with single or more null values in a Table, the Coalesce() function is very useful.

 

 

 

 

ISNULL

Replaces NULL with the specified replacement value.

 

 

 

 

 

 

 

Blogger Labels: NULL,Operations,Server,Coalesce,Table,ISNULL,Replaces

Object Oriented Programming Vs Abstract Class/Interface

 

 

Object-oriented programming (OOP) is a programming paradigm that represents concepts as "objects" that have data fields (attributes that describe the object) and associated procedures known as methods. Objects, which are usually instances of classes, are used to interact with one another to design applications and computer programs.

 

 

Encapsulation

Encapsulation refers to the creation of self-contained modules that bind processing functions to the data. These user-defined data types are called classes. Each class contains data as well as a set of methods which manipulate the data. The data components of a class are called instance variables and one instance of a class is an object. For example, in a library system, a class could be member, and John and Sharon could be two instances (two objects) of the library class.

 

 

 

 

Inheritance

Classes are created according to hierarchies, and inheritance allows the structure and methods in one class to be passed down the hierarchy. That means less programming is required when adding functions to complex systems. If a step is added at the bottom of a hierarchy, then only the processing and data associated with that unique step needs to be added. Everything else about that step is inherited. The ability to reuse existing objects is considered a major advantage of object technology.

 

 

 

 

 

Polymorphism

Object-oriented programming allows procedures about objects to be created whose exact type is not known until runtime. For example, a screen cursor may change its shape from an arrow to a line depending on the program mode. The routine to move the cursor on screen in response to mouse movement would be written for “cursor,” and polymorphism allows that cursor to take on whatever shape is required at runtime. It also allows new shapes to be easily integrated.

 

 

 

 

 

What is an Abstract Class?

An abstract class is a special kind of class that cannot be instantiated. So the question is why we need a class that cannot be instantiated? An abstract class is only to be sub-classed (inherited from). In other words, it only allows other classes to inherit from it but cannot be instantiated. The advantage is that it enforces certain hierarchies for all the subclasses. In simple words, it is a kind of contract that forces all the subclasses to carry on the same hierarchies or standards.

 

What is an Interface?

An interface is not a class. It is an entity that is defined by the word Interface. An interface has no implementation; it only has the signature or in other words, just the definition of the methods without the body. As one of the similarities to Abstract class, it is a contract that is used to define hierarchies for all subclasses or it defines specific set of methods and their arguments. The main difference between them is that a class can implement more than one interface but can only inherit from one abstract class. Since C# doesn’t support multiple inheritance, interfaces are used to implement multiple inheritance.

 

Both Together

When we create an interface, we are basically creating a set of methods without any implementation that must be overridden by the implemented classes. The advantage is that it provides a way for a class to be a part of two classes: one from inheritance hierarchy and one from the interface.

When we create an abstract class, we are creating a base class that might have one or more completed methods but at least one or more methods are left uncompleted and declared abstract. If all the methods of an abstract class are uncompleted then it is same as an interface. The purpose of an abstract class is to provide a base class definition for how a set of derived classes will work and then allow the programmers to fill the implementation in the derived classes.

There are some similarities and differences between an interface and an abstract class that I have arranged in a table for easier comparison:

 

 

 

 

 

Blogger Labels: Object,Abstract,Class,Interface,paradigm,concepts,data,procedures,Objects,instances,computer,Encapsulation,creation,self,modules,user,components,instance,example,library,system,member,John,Sharon,Inheritance,Classes,hierarchies,hierarchy,systems,advantage,technology,Polymorphism,cursor,mode,response,implementation,signature,definition,similarities,arguments,difference,interfaces,Both,Together,purpose,programmers,differences,comparison,runtime,subclasses

Difference Between Struct (Value Type) and Class in .NET(Ref. Type)

 

Introduction:

So, what is the difference between a struct and a class? Well, if you have only ever been exposed to the Java world, there is no concept of a struct as all complex types are classes.  But if you've only ever been exposed to the C++ world, you would probably answer that C++ structs and classes are identical in every way save one: structs are public by default and classes are private by default.

So, with C++ and Java both counted in the ancestry of C#, which route did the designers of C# take when they went to implement class and stuct? Well, it turns out that C# struck out on its own and took neither side.  Struct is not simply a public-defaulted class, but they didn't eliminate it either.

C# is unique among the triumvirate in its differences between what it calls a struct and what it calls a class. While there are a lot of good articles and books that accurately describe the difference, there are also unfortunately a lot of generalizations and incomplete descriptions of the differences.

Repetition is the key to learning, and hopefully by this blog repeating the differences those who are new to C# will learn something, and those who are intermediate will refresh themselves, and those who are advanced can point out any holes I may have missed.

 

Semantics:

First of all, it is important to note that there is a strong semantic difference in people's mind when you say struct or class that needs to be considered. Typically when you tell someone you've defined a class, this tends to trigger in people's mind the idea that you've created a fully-fledged object-oriented type that follows all the basic OO rules (encapsulation, polymorphism, etc) and has its actual fields well hidden and protected.

However, when you say you've defined a struct, a lot of times people get the image of a very flat data structure with simple fields (or properties) and very few operations. That is, many people think fo structs as lightweight, flat types and classes as full-blown object oriented types.

So, regardless of what the language describes as the differences (which we will get to next section), just keep in mind that people tend to think of classes as object-oriented types and structs as flat "data-holder" types.

 

Syntactical Comparison:

Now, semantics aside, there are a lot of things that are similar between struct and class in C#, but there are also a fair number of surprising differences. Let’s look at a table that sums them up:

 

 

 

 

 

 

 

Value vs. Reference Type

This one is probably the one key difference most people will point to as the primary difference between struct and class in C#, and not surprisingly, it is the source of several potential pitfalls.

The fact that a struct is a value type, while class is a reference type. This has several ramifications:

 

With value types:

 

• Value type assignments copy all members the whole value - this copies all members of one value to another making two complete instances.
• Value types passed by parameter or returned from methods/properties copy whole value - this behavior is the same as value assignment.
• Value types assigned to an object are boxed – that is, they are surrounded by an object and then passed by reference.
• Value types are destroyed when they pass out of scope - local variables and parameters are typically cleaned up when scope is exited, members of an enclosing type are cleaned up when the enclosing type is cleaned up.
• Value types may be created on the stack or heap as appropriate - typically parameters and locals are created on the stack, members of an enclosing class are typically on the heap.
• Value types cannot be null - default value of members that are primitive is zero, default value of members that are a struct is an instance with all struct members defaulted.

 

In contrast, with reference types:

 

• Reference type assignment only copies the reference - this decrements/increments reference counts as appropriate.
• Reference types passed by parameter or returned from methods/properties pass a reference - the reference is copied, but both references refer to the same original object.
• Reference types are only destroyed when garbage collected - after all references to the object are determined to be unreachable.
• Reference types are generally on the heap - it’s always possible compiler may optimize, but in general you should always think of them as heap objects
• Reference types can be null - default value of members that are reference types members is null.

 

 

 

 

 

Blogger Labels: Difference,Struct,Value,Type,Class,Introduction,Java,concept,ancestry,designers,triumvirate,differences,generalizations,descriptions,Repetition,Semantics,data,properties,holder,Syntactical,Comparison,Reference,pitfalls,fact,ramifications,instances,parameter,assignment,scope,parameters,locals,zero,instance,increments,references,garbage

Database Dependencies

 

Functional Dependencies

A dependency occurs in a database when information stored in the same database table uniquely determines other information stored in the same table. You can also describe this as a relationship where knowing the value of one attribute (or a set of attributes) is enough to tell you the value of another attribute (or set of attributes) in the same table.

Saying that there is a dependency between attributes in a table is the same as saying that there is a functional dependency between those attributes. If there is a dependency in a database such that attribute B is dependent upon attribute A, you would write this as “A -> B”.

For example, In a table listing employee characteristics including Social Security Number (SSN) and name, it can be said that name is dependent upon SSN (or SSN -> name) because an employee's name can be uniquely determined from their SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but different SSNs.

 

Full Functional Dependencies

A full functional dependency occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any farther. For example, “{SSN, age} -> name” is a functional dependency, but it is not a full functional dependency because you can remove age from the left side of the statement without impacting the dependency relationship.

 

Transitive Dependencies

Transitive dependencies occur when there is an indirect relationship that causes a functional dependency. For example, ”A -> C” is a transitive dependency when it is true only because both “A -> B” and “B -> C” are true.

 

Trivial Functional Dependencies

A trivial functional dependency occurs when you describe a functional dependency of an attribute on a collection of attributes that includes the original attribute. For example, “{A, B} -> B” is a trivial functional dependency, as is “{name, SSN} -> SSN”. This type of functional dependency is called trivial because it can be derived from common sense. It is obvious that if you already know the value of B, then the value of B can be uniquely determined by that knowledge.

 

Multivalued Dependencies

Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table. For example, imagine a car company that manufactures many models of car, but always makes both red and blue colors of each model. If you have a table that contains the model name, color and year of each car the company manufactures, there is a multivalued dependency in that table. If there is a row for a certain model name and year in blue, there must also be a similar row corresponding to the red version of that same car.

 

Importance of Dependencies

Database dependencies are important to understand because they provide the basic building blocks used in database normalization. For example:

• For a table to be in second normal form (2NF), there must be no case of a non-prime attribute in the table that is functionally dependendent upon a subset of a candidate key.
• For a table to be in third normal form (3NF), every non-prime attribute must have a non-transitive functional dependency on every candidate key.
• For a table to be in Boyce-Codd Normal Form (BCNF), every functional dependency (other than trivial dependencies) must be on a superkey.
• For a table to be in fourth normal form (4NF), it must have no multivalued dependencies.

 

Blogger Labels: Database,Dependencies,Functional,dependency,information,relationship,example,employee,characteristics,Social,Number,statement,SSNs,Full,requirements,Transitive,Trivial,collection,knowledge,presence,version,Importance,normalization,subset,candidate,Boyce,Codd,Normal,Form,BCNF,fourth,upon

Database Normalization

 

Normalization is a method for organizing data elements in a database into tables.

 

 

Normalization Avoids

• Duplication of Data – The same data is listed in multiple lines of the database
• Insert Anomaly – A record about an entity cannot be inserted into the table without first inserting information about another entity – Cannot enter a customer without a sales order
• Delete Anomaly – A record cannot be deleted without deleting a record about a related entity. Cannot delete a sales order without deleting all of the customer’s information.
• Update Anomaly – Cannot update information without changing information in many places. To update customer information, it must be updated for each sales order the customer has placed

Normalization is a three stage process – After the first stage, the data is said to be in first normal form, after the second, it is in second normal form, after the third, it is in third normal form

 

Before Normalization

1. Begin with a list of all of the fields that must appear in the database. Think of this as one big table.

2. Do not include computed fields

3. One place to begin getting this information is from a printed document used by the system.

4. Additional attributes besides those for the entities described on the document can be added to the database.

 

Before Normalization – Example

See Sales Order from below:

 

Fields in the original data table will be as follows:

SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName, ItemNo, Description, Qty, UnitPrice

Think of this as the baseline – one large table

 

First Normal Form (1NF)  

First normal form (1NF) sets the very basic rules for an organized database:

· Eliminate duplicative columns from the same table.

· Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

 

• Separate Repeating Groups into New Tables.
• Repeating Groups Fields that may be repeated several times for one document/entity
• Create a new table containing the repeating data
• The primary key of the new table (repeating group) is always a composite key; Usually document number and a field uniquely describing the repeating line, like an item number.

 

 

 

First Normal Form Example

 

The new table is as follows:

SalesOrderNo, ItemNo, Description, Qty, UnitPrice

The repeating fields will be removed from the original data table, leaving the following.

SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName

These two tables are a database in first normal form

 

 

What if we did not Normalize the Database to First Normal Form?

Repetition of Data – SO Header data repeated for every line in sales order.

 

 

 

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

· Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

 

• Remove Partial Dependencies.
• Functional Dependency The value of one attribute in a table is determined entirely by the value of another.
• Partial Dependency A type of functional dependency where an attribute is functionally dependent on only part of the primary key (primary key must be a composite key).
• Create separate table with the functionally dependent data and the part of the key on which it depends. Tables created at this step will usually contain descriptions of resources.

 

 

 

Second Normal Form Example

The new table will contain the following fields:

ItemNo, Description

All of these fields except the primary key will be removed from the original table. The primary key will be left in the original table to allow linking of data:

SalesOrderNo, ItemNo, Qty, UnitPrice

Never treat price as dependent on item. Price may be different for different sales orders (discounts, special customers, etc.)

Along with the unchanged table below, these tables make up a database in second normal form:

SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName

 

 

What if we did not Normalize the Database to Second Normal Form?

• Repetition of Data – Description would appear every time we had an order for the item
• Delete Anomalies – All information about inventory items is stored in the SalesOrderDetail table. Delete a sales order, delete the item.
• Insert Anomalies – To insert an inventory item, must insert sales order.
• Update Anomalies – To change the description, must change it on every SO.

 

 

 

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

· Meet all the requirements of the second normal form.

· Remove columns that are not dependent upon the primary key.

 

• Remove transitive dependencies.
• Transitive Dependency A type of functional dependency where an attribute is functionally dependent on an attribute other than the primary key. Thus its value is only indirectly determined by the primary key.
• Create a separate table containing the attribute and the fields that are functionally dependent on it. Tables created at this step will usually contain descriptions of either resources or agents. Keep a copy of the key attribute in the original file.

 

 

 

Third Normal Form Example

The new tables would be:

CustomerNo, CustomerName, CustomerAdd

ClerkNo, ClerkName

All of these fields except the primary key will be removed from the original table. The primary key will be left in the original table to allow linking of data as follows:

SalesOrderNo, Date, CustomerNo, ClerkNo

Together with the unchanged tables below, these tables make up the database in third normal form.

ItemNo, Description

SalesOrderNo, ItemNo, Qty, UnitPrice

 

 

What if we did not Normalize the Database to Third Normal Form?

• Repetition of Data – Detail for Cust/Clerk would appear on every SO
• Delete Anomalies – Delete a sales order, delete the customer/clerk
• Insert Anomalies – To insert a customer/clerk, must insert sales order.
• Update Anomalies – To change the name/address, etc, must change it on every SO.

 

Completed Tables in Third Normal Form

Customers: CustomerNo, CustomerName, CustomerAdd

Clerks: ClerkNo, ClerkName

Inventory Items: ItemNo, Description

Sales Orders: SalesOrderNo, Date, CustomerNo, ClerkNo

SalesOrderDetail: SalesOrderNo, ItemNo, Qty, UnitPrice

 

 

Boyce-Codd Normal Form (BCNF or 3.5NF)

The Boyce-Codd Normal Form, also referred to as the "third and half (3.5) normal form", adds one more requirement:

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables

· Meet all the requirements of the third normal form.

· Every determinant must be a candidate key.

 

 

Fourth Normal Form (4NF)

Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

Finally, fourth normal form (4NF) has one additional requirement:

· Meet all the requirements of the third normal form.

· A relation is in 4NF if it has no multi-valued dependencies.

Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

Definition: Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table.

For more detail, read What is a Database Dependency?

 

Examples:

For example, imagine a car company that manufactures many models of car, but always makes both red and blue colors of each model. If you have a table that contains the model name, color and year of each car the company manufactures, there is a multivalued dependency in that table. If there is a row for a certain model name and year in blue, there must also be a similar row corresponding to the red version of that same car.

 

Example

Consider this example of a database of teaching courses, the books recommended for the course, and the lecturers who will be teaching the course:

Course	Book	Lecturer
AHA	Silberschatz	John D
AHA	Silberschatz	William M
AHA	Silberschatz	Christian G
AHA	Nederpelt	William M
AHA	Nederpelt	John D
AHA	Nederpelt	Christian G
AHA	MYBOOK	William M
OSO	Silberschatz	John D
OSO	Silberschatz	William M

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.

 

5^th Normal Form

Fifth normal form (5NF), also known as Project-join normal form (PJ/NF) is a level of database normalization designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships.

Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

 

Example

Consider the following example:

Travelling Salesman Product Availability By Brand
Travelling Salesman	Brand	Product Type
Jack Schneider	Acme	Vacuum Cleaner
Jack Schneider	Acme	Breadbox
Willy Loman	Robusto	Pruning Shears
Willy Loman	Robusto	Vacuum Cleaner
Willy Loman	Robusto	Breadbox
Willy Loman	Robusto	Umbrella Stand
Louis Ferguson	Robusto	Vacuum Cleaner
Louis Ferguson	Robusto	Telescope
Louis Ferguson	Acme	Vacuum Cleaner
Louis Ferguson	Acme	Lava Lamp
Louis Ferguson	Nimbus	Tie Rack

The table's predicate is: Products of the type designated by Product Type, made by the brand designated by Brand, are available from the travelling salesman designated by Travelling Salesman.

In the absence of any rules restricting the valid possible combinations of Travelling Salesman, Brand, and Product Type, the three-attribute table above is necessary in order to model the situation correctly.

Suppose, however, that the following rule applies: A Travelling Salesman has certain Brands and certain Product Types in his repertoire. If Brand B is in his repertoire, and Product Type P is in his repertoire, then (assuming Brand B makes Product Type P), the Travelling Salesman must offer products of Product Type P made by Brand B.

 

In that case, it is possible to split the table into three: Product Types By Travelling Salesman
Travelling Salesman             Product Type
Jack Schneider	Vacuum Cleaner
Jack Schneider	Breadbox
Willy Loman	Pruning Shears
Willy Loman	Vacuum Cleaner
Willy Loman	Breadbox
Willy Loman	Umbrella Stand
Louis Ferguson	Telescope
Louis Ferguson	Vacuum Cleaner
Louis Ferguson	Lava Lamp
Louis Ferguson	Tie Rack

Brands By Travelling Salesman
Travelling Salesman	Brand
Jack Schneider	Acme
Willy Loman	Robusto
Louis Ferguson	Robusto
Louis Ferguson	Acme
Louis Ferguson	Nimbus

Product Types By Brand
Brand	Product Type
Acme	Vacuum Cleaner
Acme	Breadbox
Acme	Lava Lamp
Robusto	Pruning Shears
Robusto	Vacuum Cleaner
Robusto	Breadbox
Robusto	Umbrella Stand
Robusto	Telescope
Nimbus	Tie Rack

 

 

 

Blogger Labels: Database,Normalization,method,data,Avoids,Duplication,Insert,information,customer,sales,Delete,Update,Begin,Think,system,Additional,entities,Example,Order,Fields,SalesOrderNo,Date,CustomerNo,CustomerName,CustomerAdd,ClerkNo,ClerkName,ItemNo,Description,UnitPrice,Normal,Form,Eliminate,columns,Create,column,Separate,Groups,Tables,item,Normalize,Repetition,Header,Second,concept,Meet,requirements,Remove,subsets,relationships,predecessors,Partial,Dependencies,Functional,Dependency,descriptions,resources,Price,customers,Along,Anomalies,inventory,items,SalesOrderDetail,Third,Transitive,Thus,agents,Keep,Together,Detail,Cust,Clerk,Clerks,Orders,Boyce,Codd,BCNF,requirement,candidate,determinant,Fourth,Isolate,Independent,Multiple,relation,Remember,guidelines,criteria,Definition,presence,Examples,version,Consider,lecturers,Course,Book,Lecturer,Silberschatz,John,William,Christian,Nederpelt,MYBOOK,vice,Fifth,Project,redundancy,databases,Salesman,Product,Brand,Type,Jack,Schneider,Acme,Vacuum,Cleaner,Breadbox,Loman,Robusto,Shears,Umbrella,Stand,Louis,Ferguson,Telescope,Lava,Lamp,Nimbus,Rack,Products,absence,combinations,situation,Suppose,Brands,Types,repertoire,three,duplicative,multi

Temporary Tables in SQL Server

 

Introduction

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

In this article, I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article. 

 

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

· Local Temp Table

· Global Temp Table

 

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

 

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

 

Creating Temporary Table in SQL Server 2005

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server 2005:

 

CREATE TABLE #LocalTempTable(

UserID int,

UserName varchar(50), 

UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in thetemporary table similar to a general table like:

 

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

 

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select"Command, it will throw the following error:

 

Msg 208, Level 16, State 0, Line 1

Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

 

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##"with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table: 

 

CREATE TABLE ##NewGlobalTempTable(

UserID int,

UserName varchar(50), 

UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in thetemporary table similar to a general table like:

 

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

 

Storage Location of Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

 

 

Now, if we deeply look into the name of Local Temporary table names, a 'dash' is associated with each and every table name along with an ID. Have a look at the image below:

 

SQL server does all this automatically, we do not need to worry about this; we need to only use the table name. 

 

When to Use Temporary Tables?

Below are the scenarios where we can use temporary tables:

· When we are doing large number of row manipulation in stored procedures.

· This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.

· When we are having a complex join operation.

 

 

Points to Remember Before Using Temporary Tables

 

· Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.

· Number of rows and columns need to be as minimum as needed.

· Tables need to be deleted when they are done with their work.

 

 

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

 

Declare @TempTableVariable TABLE(

UserID int,

UserName varchar(50), 

UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:

 

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:

select * from @TempTableVariable

 

When to Use Table Variable Over Temp Table

Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

 

Blogger Labels: Temporary,Tables,Server,Introduction,concept,developer,kinds,scope,database,article,overview,beginners,suggestions,feedback,Different,Types,Local,Temp,Table,Global,connection,user,instances,Once,Here,syntax,CREATE,LocalTempTable,UserID,UserName,script,Abhijit,India,execution,statements,Insert,Select,Command,error,Level,State,Line,Invalid,NewGlobalTempTable,users,Storage,Location,Folder,scenarios,procedures,cursor,data,Points,Remember,performance,Number,columns,Alternative,Approach,Variable,Declare,TempTableVariable,scripts,Tablevariable,Over,tempdb,varchar

Security of MS SQL Server

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Example of Managing Server Principles

 

 

 

 

Examples of DB Principles

 

 

 

 

 

 

 

 

 

Example of Role Principals

 

 

 

Objects

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Example of Authorizing Principals

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Example using Permissions

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Example of Impersonation

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To Set default user to SQL server

 

 

 

 

 

 

 

Examples of Groups or Roles

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Schemas Example

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Roles Example

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Execution Context

 

 

 

 

 

 

 

 

 

 

Database level execution Context

 

DB Context span only across only on current data base

 

 

 

Server level execution Context

 

Login context span across Entire server

 

 

 

 

 

 

 

Blogger Labels: Server,Example,Principles,Examples,Role,Principals,Objects,Permissions,Impersonation,user,Groups,Roles,Schemas,Execution,Context,Database,data,Login,Entire