NULLIF Function in sql

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASEstatement:

SELECT CASE ("column_name")
  WHEN "expression 1 = expression 2 " THEN "NULL"
  [ELSE "expression 1"]
  END
FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

Store_Name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;

Result:

Store_Name	NULLIF (Actual, Goal)
Store A	NULL
Store B	40
Store C	25

Delete duplicate rows in sql using Single query

To delete the Newly inserted Duplicate record :

create table ##temp (id int identity(1,1) primary key  ,name varchar(100) )

insert into ##temp (name) values ('Kumar')
select * from ##temp


insert into ##temp (name) values ('Kumar')
select * from ##temp






Delete a from ##temp a,##temp  b where a.id>b.id and a.name=b.name
select * from ##temp

To delete the Old inserted Duplicate record :

create table ##temp (id int identity(1,1) primary key  ,name varchar(100) )

insert into ##temp (name) values ('Kumar')
select * from ##temp


insert into ##temp (name) values ('Kumar')
select * from ##temp






Delete a from ##temp a,##temp  b where a.id<b.id and a.name=b.name
select * from ##temp

QueryString Using PostBackUrl

<asp:LinkButton ID="lnkfirstname" runat="server" Text='<%# Eval("firstname") %>' PostBackUrl='<%# String.Format("~/Personal/PersonalDetails.aspx?EMP_ID={0}",Eval("EMP_id")) %>'> </asp:LinkButton>

Difference between cellpadding and cellspacing

Example 1 clearly illustrates the difference between cellpadding and cellspacing:

Example 1 - (cellpadding="10" cellspacing="10")

Example 1 - Legend

 

Cell content

 

Cellpadding

.....

Cell border

 

Cellspacing

 

 

Table border

Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order.

Example:

CREATE TABLE ##TestTable

(NewIDCol uniqueidentifier DEFAULT NEWID(),

NewSeqCol uniqueidentifier DEFAULT NewSequentialID())

----Inserting five default values in table

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

----Test Table to see NewID() is random

----Test Table to see NewSequentialID() is Incremental Sequence

SELECT *

FROM ##TestTable

----Clean up database with droping column

--DROP TABLE ##TestTable

GO

Output:

Function NEWSEQUENTIALID() can not be used in SQL queries and it can be only used in DEFAULT clause of table. NEWSEQUENTIALID() are predictable, in case of privacy or security use NEWID() instead of NEWSEQUENTIALID(). If tried to use in QUERY it will thrown an error. NEWID() will work perfectly fine when used in queries.

Example:
----This will thrown an errorSELECT NEWSEQUENTIALID()GO

ResultSet:
Msg 302, Level 16, State 0, Line 1
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type ‘uniqueidentifier’ in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.
----This will return GUIDSELECT NEWID()GO
ResultSet:
————————————
26CE6817-B138-413A-92AD-A2F2BBF7E0B8

Additional Information from Book On-Line

The GUIDs generated by NEWSEQUENTIALID() are unique only within a particular computer if the computer does not have a network card.

You can use NEWSEQUENTIALID() to generate GUIDs to reduce page contention at the leaf level of indexes.

The value returned by NEWID is different for each computer.

coding for window 10 file download from server

string _localhost = "http://localhost:XXXXX/";

string _fileType= "video/mp4";

 public async void Filedownload(IList<TableContract> tableList)

        {

            string _pathforImage = Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Image";

            string _pathforVideo= Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Video";

            string _folderPath;

            FolderCreation(_pathforImage);

            FolderCreation(_pathforVideo);

            foreach (var NewtableList in tableList)

            {

                _folderPath = NewtableList.tableType.Trim() == _fileType ? _pathforVideo : _pathforImage;

                string FilePath = NewtableList.ContentPath;

                using (var httpClient = new HttpClient())

                {

                    using (var request = new HttpRequestMessage(HttpMethod.Get, _localhost + FilePath.Remove(0, 2)))

                    {

                        using (

                            Stream contentStream = await (await httpClient.SendAsync(request)).Content.ReadAsStreamAsync(),

                          stream = new FileStream(_folderPath + "\\"+ NewtableList.FileName, FileMode.Create, FileAccess.Write, FileShare.None))

                        {

                            await contentStream.CopyToAsync(stream);

                        }

                    }

                }

            }

           

        }

        public void FolderCreation(string Path)

        {

            if (!Directory.Exists(Path))

            {

                Directory.CreateDirectory(Path);

            }

        }

Default error page in web.config

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <system.web>

         <customErrors defaultRedirect="error.htm" mode="On" />

    </system.web>

</configuration>