SQL Server Nested Select

Facebooktwitterredditpinterestlinkedinmail

SQL Server nested select is also known as a “sub query”. This is where instead of using a list, table, or view, you actually use another query. In this article, we will go over the basics on how to use a SQL Server nested select statement.

Basic Rules For A Nested Select
Here are a couple basic rules when using a nested select statement in SQL Server.

  • The nested select statement must be enclosed in parentheses.
  • When using an ORDER BY clause in your nested select, you must specify TOP command.
  • Many times, you will need to alias the nested select.

 
Sample Nested Select
Here is a sample where we return the basic animal information with their average weight.

SELECT	A.AnimalID,
		A.AnimalName,
		W. AverageWeight
FROM	Animal A
INNER	JOIN (SELECT	AnimalID,
				  		AVG(AnimalWeight) AS AverageWeight
	          FROM		AnimalWeights
	          GROUP		BY AnimalID) W ON W.AnimalID = A.AnimalID

In the above example, we do our normal inner join, but we join to a nested select statement. This allows you to join on the results from the nested select. You can see that the nested select statement will get the average weight of the animals by AnimalID. That whole query is inside parenthesis. Then we alias that nested select results as “W”. We can then reference the 2 return values (AnimalID and AverageWeight) outside of the nested select statement by using the table alias W.

 
 
NOTE: Remember that you will need to alias your nested select statements most of the time. This can stop you up if you aren’t looking for it.

What Is ANSI_PADDING

Facebooktwitterredditpinterestlinkedinmail

If you work with stored procedures or if you script objects in SQL Server, you have probably seen SET statements that turn on/off some crazy thing called ANSI_PADDING. If you’re like most people, you just ignore this because it works. I wanted to take a few minutes to explain what this ANSI_PADDING thing is.

 
What Is ANSI_PADDING?
ANSI_PADDING is an option that controls how VARCHAR and VARBINARY values are stored. If ANSI_PADDING is turned on, then SQL Server will NOT trim the trailing spaces when it inserts into a VARCHAR field. Similarly, it will NOT trim trailing nulls when it inserts into a VARBINARY field.

 
Setting the ANSI_PADDING only affects the spaces on inserts. It does NOT affect comparisons.

Syntax For Setting The ANSI_PADDING:

SET	ANSI_PADDING ON
GO

INSERT
INTO	Animal
	(AnimalName)
VALUES	('Dog    ')

GO

SET	ANSI_PADDING OFF
GO

SQL Rank Vs Row Number

Facebooktwitterredditpinterestlinkedinmail

SQL Rank Vs Row Number is a common question because the two functions are very similar. SQL Server has the two similar built-in functions to assign numbers to the order of the data in the result set. The SQL Server RANK function and the ROW_NUMBER function both can accomplish this, but they do something slightly different. You can follow these links see details on how to use this functions at RANK function and ROW_NUMBER function.

ROW_NUMBER: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will continue the sequence on the duplicated record… ensuring that the row number sequence is never duplicated.

RANK: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will use the same number for the duplicate records. There will then be a gap in the sequence for the duplicated records.

 
Reference:
    RANK: http://msdn.microsoft.com/en-us/library/ms176102.aspx
    ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734.aspx

What Is SQL Server

Facebooktwitterredditpinterestlinkedinmail

What Is SQL Server
SQL Server is a database management system (DBMS) produced by Microsoft. The purpose of SQL Server is to store and retrieve data for use in computer applications. These applications could be mobile apps, web sites, desktop applications, or more.

Data in SQL Server is stored in tables. These tables have rows and columns, much like you would see in an Excel spreadsheet. SQL Server accesses this data using a coding language called T-SQL (Transact-SQL). This code is called a query. Here is a sample query that pulls all of the data from a table.

SELECT	*
FROM	AnimalTable

 
Easiest Definition Of “What Is SQL Server”
SQL Server is a program built by Microsoft that other programs can use to store and retrieve data.