SQL Server Series: select to retrieve data

1. Select the first n rows of data

You can use top in SQL Server to specify the returned rows. It is important for the top and with ties options. It can append multiple rows to the result set returned by top. These rows have the same value as the last row of the result set returned by top in the sorting column specified by the order by clause.

 
Select Top 3 WithTies* FromDBO. ProductOrder ByQuantity
 

2. Use null values

2.1 check for null values

SQL introduces a special operator "is" to check the equivalence between special values. Its Syntax format is as follows:

 
Where Expression Is Null
 

2.2 handle null values

The most common null value processing function is isnull (). Its Syntax format is as follows:

Isnull(Source_expression, replacement_value)
 

3. scalar functions

3.1 server environment information

System functions can return information about the environment.

◊ Getdate (): returns the current server date and time with a precision of 3 milliseconds.

Mongodb_name (): returns the name of the current database.

◊ Serverproperty (): Use the serverproperty (property) function to obtain useful information about the server, including:

1> collation: the default server sorting rule name.

2>. Edition: the version of the installed SQL Server.

3> engineedition: indicates the Database Engine version. 1 indicates an individual or a Desktop Engine, 2 indicates the Standard Edition, and 3 indicates the Enterprise Edition.

◊ InstanceName: name of the instance to which the user connects. If it is a default instance, a null value is returned.

◊ Productversion: the version number of SQL Server.

Release productlevel: version level of the SQL server instance. "RTM" indicates the release version, "SPNs" indicates service packs, and "bn" indicates the beta version.

◊ Servername: The full name of the server and SQL server instance.

3.2 user information functions

◊ User_name (): returns the database username of the current user.

◊ Suser_name (): the login name used to identify users using SQL Server.

◊ Host_name (): return the name of the user workstation.

◊ App_name (): returns the application currently connected to SQL Server.ProgramName.

4. Create a view

Normally, a view cannot contain an order by clause. If you want to sort View data, you should generally use the order by clause in the SELECT statement of the query view. SQL Server allows the view to use top, because top is generally useless if the order by clause is not used for sorting. Therefore, if the view contains Top 100 percent, it can use the order by clause.

Create ViewVproductAsSelect Top 100 Percent * FromProductOrder ByProductno