CHAR is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column. OR It stores fixed length of character. if you declare char(50) then it allocates memory for 50 characters. if you store 10 character word then it store it in 10 characters memory location and other 40 character's memory location will be wasted. Char datatype can store upto 8000 bytes of fixed-length character. VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.Read More

I will explain how to validate Radiobuttonlist using Jquery using Asp.net. Script For Validation Design Page : Read More

Web Service is an application that is designed to interact directly with other applications over the internet. In simple sense, Web Services are means for interacting with objects over the Internet. The Web serivce consumers are able to invoke method calls on remote objects by using SOAP and HTTP over the Web. WebService is language independent and Web Services communicate by using standard web protocols and data formats, such as HTTP XML SOAP Advantages of Web Service Web Service messages are formatted as XML, a standard way for communication between two incompatible system. And this message is sent via HTTP, so that they can reach to any machine on the internet without being blocked by firewall. Examples for Web Service Weather Reporting: You can use Weather Reporting web service to display weather information in your personal website. Stock Quote: You can display latest update of Share market with Stock Quote on your web site.Read More

The Page having a FileUpload control and the Upload button, on selecting the Excel or CSV file user needs to click on Upload button to store the data to Server. Here we are treating the uploaded file as database hence we need to create OLEDB connection to this file, from this connection will be created and the data is fetched to C# as DataTable. '[Sheet1$]' is the Name of the Worksheet where requires data is present. Table CREATE TABLE [dbo].[Tag]( [ID] [int] IDENTITY(1,1) NOT NULL, [TagType] [int] NULL, [TagCode] [nvarchar](100) NULL, [Status] [bit] NULL, [Duration] [int] NULL) Stored Procedure Create proc [dbo].[ExcelTag] @TagType int, @TagCode nvarchar(100), @Status bit, @Duration int as if Not exists(Select top 1 TagCode from dbo.[Tag] where TagCode=@TagCode) Begin insert into dbo.[Tag](TagType,TagCode,[Status],Duration)values(@TagType,@TagCode,@Status,@Duration) EndRead More

GETDATE() Gives current date time (server’s time zone) E.g. SELECT GETDATE()return 2011-08-25 09:54:39.963 GETUTCDATE() Gives current date time (GMT + 0 Time zone) E.g. SELECT GETUTCDATE() return 2011-08-25 04:27:30.163 ISDATE('Value') Returns bool value whether given value is proper date or not E.g. SELECT ISDATE('2011-08-25') return 1 SELECT ISDATE('2011-13-25') return 0 LTRIM('Value') Trims from left E.g. SELECT LTRIM(' SQL SERVER ') return ‘SQL SERVER ‘ RTRIM('Value') Trims from right E.g. SELECT LTRIM(' SQL SERVER ') return ‘ SQL SERVER‘ REPLACE() replace all occurrence of string with other string E.g. SELECT REPLACE('SQL SERVER ','S','A') return ‘AQL AERVER’Read More

IntelliSense for Query Editing IntelliSense offers a few additional features besides just completing the world. You can see those options from SSMS List Members Parameter Info Quick Info Complete Word Refresh Local Cache Table-Value Parameter In many situations, it is necessary to pass a set of table structured values to a stored procedure or function. These values may be used for updating/population a table. CREATE TYPE Customer AS TABLE ( CustomerId INT IDENTITY(1,1), CustomerName VARCHAR(30) ); GO DECLARE @T as Customer INSERT INTO @T(CustomerName) VALUES ('a'),('b'),('c'),('d'),('e') SELECT * FROM @T Grouping Sets Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster. Example SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year)) ORDER BY Year, Quarter File Stream dataRead More

 

DELETE	TRUANCATE
DELETE is DML Command.	TRUNCATE is DDL Command.
DELETE Command is slow	TRUNCATE is Faster
The DELETE command is used to remove specified rows from a table.	TRUNCATE removes all rows from a table.
DELETE Can be used with or without a WHERE clause	No WHERE clause is used
DELETE operation you need to COMMIT or ROLLBACK	The operation cannot be rolled back
DELETE does not reset identity of the table.	TRUNCATE Resets identity of the table.
Trigger get fired	Trigger doesn’t get fired

https://plus.google.com/103205117009535639356Read More

Store Procedure	Function
S.P need not be return a value	Functions MUST return a value
S.P can be called independently usingexec keyword whereas	Function is called using SELECTstatements.
S.P can be used for performing business logic	Functions are used forcomputations
Can be used EXEC inside an S.P	EXEC command can’t be used inside a Function
S.P takes IN and OUT parameters.	Function parameters are always IN, no OUT is possible
Transaction related statement can be handled in S.P	Can’t be handled in function.
XML parameters passed in S.P	Can’t pass XML Parameters
S.P can affect the state of the database by using insert, delete, update and create operations.	Functions cannot affect the state of the database which means we cannot perform insert, delete, update
Parsed and Compiled during design time itself	Compiled and executed during run time

https://plus.google.com/103205117009535639356Read More

If we required comma separated values form one to column in multiple rows.

Example

CREATE TABLE EMP
(
EID INT IDENTITY,
ENAME VARCHAR(100),
DEPT VARCHAR(100)
)

INSERT INTO EMP(ENAME,DEPT)
VALUES(‘ABC’,'SALE’),
(‘PQR’,'IT’),
(‘XYZ’,'HR’),
(‘MNL’,'ADMIN’)

DECLARE @DEPT VARCHAR(255)

SELECT @DEPT=COALESCE(@DEPT + ‘, ‘, ”) + CAST(DEPT AS VARCHAR(100))
FROM EMP

SELECT @DEPT as ‘Department’

https://plus.google.com/103205117009535639356Read More

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

SELECT @@IDENTITY
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table.

SELECT IDENT_CURRENT(‘tablename’)
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

https://plus.google.com/103205117009535639356Read More