Temporary tables vs Table Variables

Temporary tables

• They are like regular tables but stored in the tempdb database.
• These tables get dropped after they have been used.
• Temporary tables are of two types,
• Local Temporary table - defined using a pound sign (#) and accessible only within the session that created it
• Global Temporary table - defined using double pound signs (##) and visible to all users.

Table Variables

• It is a data type that looks similar to a table but has a fixed lifetime scope (i.e within a function, stored procedure or batch that it is declared in)

Here are some differences between Temporary Table and Table Variables

Temporary Tables	Table Variables
Created and stored in tempdb database	Created in Memory (although it can write to tempdb)
Transaction logs are recorded for temporary tables so they are bound to transactions.	Transaction logs are not recorded for the table variables so they are not bound to any transactions. So no effect of transaction rollbacks.
Can participate in parallel operations	Cannot participate in parallel operations
The log activity remains till it is manually cleared or the server restarts	Table variable log activity is truncated immediately
SQL Server creates statistics for temporary tables	SQL Server does not create statistics for table variables
Stored procedure containing temporary tables cannot be pre-compiled	Stored procedures containing table variables can be pre-compiled
You can drop a Temporary Table	You cannot manually drop a table variable
You can create indexes on them	You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

To be more clear about the Transaction mechanism just consider the following example

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T 

The o/p of will be

old value #

new value @

SQL Views

View is nothing but saved SQL Statements or can be defined as an object that derives its data from one or more tables or in simple words we can say it as  virtual table.
(Views don't occupy storage space,Exception is indexed view..discussed in later posts).

Why to use Views:

• Query simplicity :-We may create a view using a complex query which can be easily queried later.
• security:-Views ensure the security of data by restricting access only to specific rows and columns.

Syntax:

• ToCreate:-->Create View ViewName as Query
• ToDrop:-->Drop View ViewName 
• To Modify:-->Alter View ViewName as Query

Options we can use while creating Views:
    2 important options that can be used while creating view are 
         SCHEMABINDING and ENCRYPTION.
1.SCHEMABINDING 

• This will  locks the tables being referred by the view and prevents any changes that may change the table schema.
• Requirements:-
• Objects have to be referred to by their owner names [two part name].
• SELECT * is not permitted.
• Example:- 
• Create View MyView With SchemaBinding as Select  CustomerName,CustomerId From  dbo.TblCustomer

(After creating the view, try to alter the table TblCustomer, you cant do it! This is the power of the SCHEMABINDING option.)
2.ENCRYPTION

• This will encrypts the definition of the view.Users will not be able to see the definition of the view after it is created.
• Example:
• Create View MyView With Encryption as Select CustomerName,CustomerId From dbo.TblCustomer
• Once Encrypted,there is no way to decrypt it again.So be careful when you are using this option.

Restrictions Imposed On Views are:

• A view can be created only in the current database.
• A view can be created only if there is a SELECT permission on its base table.
• Only select statement is allowed while creating view.
• A trigger cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Indexed Views:

• When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes. 
• Requirements:- 
• View definition must always return the same results from the same underlying data. 
• Views cannot use non-deterministic functions. 
• The first index on a View must be a clustered, UNIQUE index. 
• If you use Group By, you must include the new COUNT_BIG(*) in the select list. 
• It must be created with SCHEMABINDING option. 
• View definition cannot contain the following 
• TOP 
• Text, ntext or image columns 
• DISTINCT 
• MIN, MAX, COUNT, STDEV, VARIANCE, AVG 
• SUM on a nullable expression 
• A derived table 
• Rowset function 
• Another view 
• UNION 
• Subqueries, outer joins, self joins 
• Full-text predicates like CONTAIN or FREETEXT 
• COMPUTE or COMPUTE BY 
• Cannot include order by in view definition

Updatable Views:

    Views are not only read-only but also updatable. However in order to create an updatable view, the SELECT statement which defines View has to follow several following rules:

• SELECT statement must not reference to more than one table. It means it must not contain more than one table in FROM clause, other tables in JOIN statement, or UNION with other tables.
• SELECT statement must not use GROUP BY or HAVING clause.
• SELECT statement must not use DISTINCT in the selection list.
• SELECT statement must not reference to the view that is not updatable
• SELECT statement must not contain any expression (aggregates, functions, computed columns…)

Functions vs Stored Procedure

• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another rowset(which can be used in JOINs with other tables),but Procedures cant.
• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input,output parameters for it whereas functions can have only input parameters.
• Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in procedure whereas we can't go in function.

User Defined Functions In Sql

A user-defined function (UDF) is a prepared code segment that can accept parameters, process some logic, and then return some data.

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.


Scalar User-Defined Function

This are the type of user-defined functions that most developers used in various programming languages,which accepts 0 or more parameters and returns a single value.
Here Return type will be any of the scalar data type Except Text,ntext,Image and timestamp.
Example

CREATE FUNCTION whichContinent 
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end

return @return
end

Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.

print dbo.WhichContinent('USA')

select dbo.WhichContinent(Customers.Country), customers.* 
from customers

create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))

insert into test (country) 
values ('USA')

select * from test

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Country          Continent
---------------  ------------------------------
USA              North America

Inline Table-Value User-Defined Function

It returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION CustomersByContinent 
(@Continent varchar(30))
RETURNS TABLE 
AS
RETURN 
  SELECT dbo.WhichContinent(Customers.Country) as continent,
         customers.*
  FROM customers
  WHERE dbo.WhichContinent(Customers.Country) = @Continent
GO

SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')

Multi-statement Table-Value User-Defined Function

It returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )
RETURNS 
 @CustomersbyCountryTab table (
  [CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40), 
  [ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30), 
  [Address] [nvarchar] (60), [City] [nvarchar] (15),
  [PostalCode] [nvarchar] (10), [Country] [nvarchar] (15), 
  [Phone] [nvarchar] (24), [Fax] [nvarchar] (24)
 )
AS
BEGIN
 INSERT INTO @CustomersByCountryTab 
 SELECT  [CustomerID], 
   [CompanyName], 
   [ContactName], 
   [ContactTitle], 
   [Address], 
   [City], 
   [PostalCode], 
   [Country], 
   [Phone], 
   [Fax] 
 FROM [Northwind].[dbo].[Customers]
 WHERE country = @Country
 
 DECLARE @cnt INT
 SELECT @cnt = COUNT(*) FROM @customersbyCountryTab
 
 IF @cnt = 0
  INSERT INTO @CustomersByCountryTab (
   [CustomerID],
   [CompanyName],
   [ContactName],
   [ContactTitle],
   [Address],
   [City],
   [PostalCode],
   [Country], 
   [Phone],
   [Fax]  )
  VALUES ('','No Companies Found','','','','','','','','')
 
 RETURN
END
GO
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')

Asp.net User Control Vs Custom Control

Development and Deployment:-

User control:-

• Basically Designed For a single application.
• Deployed in the source form (.ascx) along with the source code of the application.
• If the same control needs to be used in more than one application, it introduces redundancy and maintenance problems.

Custom control:-

• Designed so that it can be used by more than one application.
• Deployed either in the application's Bin directory or in the global assembly cache.
• Distributed easily and without problems associated with redundancy and maintenance.

Design:-
User control:-

• Creation is similar to the way Web Forms pages are created; well-suited for rapid application development (RAD).
• Much better in cases where static contents are more than logic.

Custom control:-

• There is no direct UI availabe at Design time,so lots of code required.
• Better when we have more logic compare to content or we can say when requires dynamic contents to be displayed.

const vs. readonly

const

• A constant member is defined at compile time and cannot be changed at runtime. 
• Constants are declared as a field, using the const keyword and must be initialized as they are declared.
• Constants must be a value type (sbyte, byte, short, ushort, int, uint, long, ulong, char, float, double, decimal, orbool), an enumeration, a string literal, or a reference to null.(Since classes or structures are initialized at run time with the new keyword, and not at compile time, you can't set a constant to a class or structure.).
•  It cant be static.

Example:- 

public class MyClass { public const double PI = 3.14159; }



ReadOnly

• A read only member is like a constant in that it represents an unchanging value.
• They are declared as a field, using the readonly keyword and can be initialized at runtime,in a constructor as well being able to be initialized as they are declared
• They can be declared as static

Example:-

public class MyClass { public readonly double PI = 3.14159; }
public class MyClass { public readonly double PI; public MyClass() { PI = 3.14159; } }

Boyce-Codd normal form

It is slightly higher version Of 3 NF.
A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key.

Example:-

Conisder table TblEmployee with columns(employee_id, first_name, last_name).

In this table, the field employee_id determines first_name and last_name. Similarly, the tuple (first_name, last_name) determines employee_id.

Normalization

What is normalization?

Normalization is the process of efficiently organizing data in a database. 

There are two goals of the normalization process: 

--->eliminating redundant data

--->storing data in efficient manner

Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

What are different normalization forms?
The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF,2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen.4th and 5th nomal forms won't be discussed in this article.

First Normal Form (1NF)-Basic rules for organizing database

1.Eliminate duplicative columns from the same table.

Example:

Consider the table TblDevelopmentTeam with columns-TeamLeader,Developer1,Developer2,Developer3)

but what about the team which have only 2 devloper,3rd column is wasting the size plus if in future what if the number of developer increases.,so thats y we have to modify it as

TblDevelopmentTeam (TeamLeader,Developer)

By this we will get the flexible way for data storage.

2.Each Column must be single valued.

Example Break address as country state city....

3.Each table must have a primary key.

Second Normal Form (2NF)-Eliminate redundant data

Every Non Key attribute must be functionaly dependent upon the primary key.

Example:- Consider The TblCustomer with Columns(CustomerNo,FirstName,LastName,Country,State,City).

In above table if 2 customers reside in the same city Contry,State and city repeats for both with same values,which results redundancy.

we simply know that this 3 things are not depend on the customer,

so remove them from here,create a separate table and create relationship between 2 tables...

It will be like this

TblCustomer(CustomerNo,FirstName,LastName,AddressId)

TblAddress(AddressId,Country,State,City).

Third Normal Form (3NF)-Eliminate redundant data

Every Non Key attribute should be independent of other non-key attribute.

Example:-Consider the table TblCustomer with Columns

(CustomerNo,OrderNo,CityId,Qty,UnitPrice,Total)--Total is depend on UnitPrice and qty. so remove it

GAC

GAC (Global Assembly Cache) is where all shared .NET assembly reside. GAC is used in the following situations:-

• If the assembly has to be shared among several application.
• If the assembly has some special security, requirements like only administrators can remove the assembly. If the assembly is private then a simple delete of assembly the assembly file will remove the assembly.

Allow numeric values only in input field using jQuery

$("#input1").keypress(function(e)
{
  // allow digits only
  if (e.which != 8 && e.which != 0 && (e.which < 48 || e.which > 57))
  {
    return false;
  }
});

Exe vs Dll

1.EXE is an extension used for executable files while DLL is the extension for a dynamic link library.
2.An EXE file can be run independently while a DLL is used by other applications.
3.An EXE file defines an entry point while a DLL does not.
4.A DLL would share the same process and memory space of the calling application while an EXE creates its separate process and memory space

Assembly Manifest

• Every assembly, contains a collection of data that describes how the elements in the assembly relate to each other. The assembly manifest contains this assembly metadata. 
• An assembly manifest contains all the metadata needed to specify the assembly's version requirements and security identity, and all metadata needed to define the scope of the assembly and resolve references to resources and classes. 
• The assembly manifest can be stored in either a PE file (an .exe or .dll) with Microsoft intermediate language (MSIL) code or in a standalone PE file that contains only assembly manifest information.

The following illustration shows the different ways the manifest can be stored.

Types of assemblies

Asp.net Cookies

What is Cookie?
When user visits a website for the first time a .txt file is sent to user's web browser and stored in a user system in a particular location, This text file is termed as cookie.

---

Use of cookie?
The main purpose of cookies is to know the user who visited websites. For instance, you can also save a small piece of information inside cookies.
You might have seen almost every website login page having one checkbox "Remember Me on this computer". These websites store the login information like username and password inside the cookies. So, next time when user logs in, the website retrieves the information from cookies and you will be logged in automatically.

---

Types of cookies:
1) Session Cookies: This cookie is stored temporary in the client machine. That means, when user closes the browser this session will be deleted from the client machine. This cookie is also called transient cookies.
2)Persistent Cookies: Persistent cookies is stored on the client hard drive in a particular location until it is not expired. That means persistent cookies hold the information until its expiry date and time.

---

Lets see how to work with cookies.
Like every Server side programming language ASP.NET also have Cookies class to deal with cookies.

To assign the value into cookies

HttpCookie myCookie = new HttpCookie("myCookie"); //Create the instance of HttpCookie class
myCookie.Values.Add("username", "MyUserName"); // Add Key and its value to Cookies
myCookie.Values.Add("password", "MyPassword"); // add Another key and value.

We have stored the value into cookies now we will see how to retirve value from cookies
To retrive the value from Cookies

HttpCookie cookie = Request.Cookies.Get("myCookie");
string UserName = cookie.Values["username"];
string Password = cookie.Values["password"];

NOTE : You should not store any critical information into cookies, as its visible and stored into client machine.

Assembly

       Dot NET assemblies are unit of deployment which may or may not be executable, i.e., they might exist as the executable (.exe) file or dynamic link library (DLL) file. 

      Assembly consists manifest-which is a data structure which stores information about an assembly

The information includes version information, list of constituent files etc.

(constituent files can include any file types like image files, text files etc. along with DLLs or EXEs) 

One assembly can contain one or more files

There are two kind of assemblies in .NET;

• private 
• shared

Private assemblies 

          The assembly which is used only by a single application is called as private assembly. 

Suppose you created a DLL which encapsulates your business logic. This DLL will be used by your client application only and not by any other application. In order to run the application properly your DLL must reside in the same folder in which the client application is installed. Thus the assembly is private to your application.

Shared assemblies (also called strong named assemblies) 

        Suppose that you are creating a general purpose DLL which provides functionality which will be used by variety of applications. Now, instead of each client application having its own copy of DLL you can place the DLL in 'global assembly cache'. Such assemblies are called as shared assemblies.

       For all calling assemblies within the same application, the same copy of the shared assembly is used from its original location. Hence, shared assemblies are not copied in the private folders of each calling assembly. 

       Each shared assembly has a four part name including its face name, version, public key token and culture information. The public key token and version information makes it almost impossible for two different assemblies with the same name or for two similar assemblies with different version to mix with each other.

Global Assembly Cache - Global assembly cache is nothing but a special disk folder where all the shared assemblies will be kept. It is located under :\WinNT\Assembly folder.

CLS and CTS

What is CTS?
        In order that two language communicate smoothly CLR has CTS (Common Type
System).Example in VB you have “Integer” and in C++ you have “long” these datatypes are not
compatible so the interfacing between them is very complicated. In order that these two different
languages communicate Microsoft introduced Common Type System. So “Integer” data type in
VB6 and “int” data type in C++ will convert it to System.int32, which is data type of CTS. CLS,
which is covered in the coming question, is subset of CTS.


What is a CLS
      This is a subset of the CTS, which all .NET languages are expected to support.Microsoft has defined CLS, which are nothing but guidelines, that language should follow so that it can communicate with other .NET languages in a seamless manner.