TOP and PERCENT in SQL

TOP  

What is the “TOP“? 

The “TOP” keyword is used to limit the number of rows returned by a SELECT statement in SSMS. It allows users to specify the number of rows that they want to retrieve from a query. This keyword is commonly used in combination with the ORDER BY clause to retrieve a specific set of data. 

Example: 

Let’s consider the following table named “Employees”:

If we want to retrieve the top three highest-paid employees from the “Employees” table, we can use the following SQL query: 


The output of this query would be: 

Advantages of using “TOP” in SSMS: 

  • Efficiency: The “TOP” keyword can significantly improve the performance of a query by limiting the number of rows returned. This can be especially useful when working with large tables. 
  • Flexibility: The “TOP” keyword is highly flexible and can be used with various clauses such as ORDER BY, WHERE, and JOIN, making it a versatile tool for data retrieval. 
  • Simplicity: The “TOP” keyword is easy to use and understand, making it an excellent choice for beginners and advanced users alike. 

Disadvantages of using “TOP” in SSMS: 

  • Limited functionality: While the “TOP” keyword is a useful tool for limiting the number of rows returned, it does not allow for more complex filtering or aggregation functions that can be achieved using other keywords and clauses. 
  • Potential for incorrect results: If the ORDER BY clause is not used correctly with the “TOP” keyword, it can lead to inaccurate results, making it essential to use this keyword with caution. 
  • Limited compatibility: While the “TOP” keyword is a standard feature in SSMS, it may not be supported in other database systems or versions of SQL, making it important to consider compatibility when using this keyword. 

Conclusion: 

In conclusion, the “TOP” keyword is an essential tool for data retrieval in SSMS. It offers a simple and efficient way to limit the number of rows returned by a query, making it an excellent choice for beginners and advanced users alike. While there are some disadvantages to using this keyword, such as limited functionality and potential for incorrect results, these can be mitigated by using the keyword with caution and ensuring that it is compatible with your database system. Overall, the “TOP” keyword is a valuable asset in the SSMS toolset and should be included in every user’s SQL toolbox. 

Percent  

Use of the Percent after the SELECT Statement in SSMS 

The percent after the SELECT statement in SSMS is used to retrieve a percentage of data from a table or column. Percent is used in conjunction with the TOP keyword to specify the percentage of data to be retrieved. 

Example: 

 SELECT TOP 10 PERCENT * FROM [Sales].[customers]; 

SQL server did not permit the use of the ORDER BY command when creating a VIEW. However, by including the PERCENT keyword in the SELECT query, the query can be executed without any issues. 

Example: 

 CREATE VIEW ViewName  
 AS 
 SELECT  TOP 100 PERCENT *  
 FROM [HR].[Employees]  
 ORDER BY [firstname] 

This query will retrieve the top 10% of rows from the Hr.Employees table. 

Advantages of Using the Percent Symbol After the SELECT Statement in SSMS 

  • Efficiency: Using the percent symbol after the SELECT statement in SSMS can make queries more efficient by reducing the amount of data retrieved. This can be particularly useful when working with large datasets. 
  • Flexibility: The percent symbol after the SELECT statement in SSMS allows users to retrieve a specific percentage of data without having to write complex queries. This can be especially useful when working with incomplete or changing data. 
  • Customization: The percent symbol after the SELECT statement in SSMS can be combined with other SQL keywords and operators to create customized queries that meet specific needs. This can make queries more powerful and versatile. 

Disadvantages of Using the Percent Symbol After the SELECT Statement in SSMS 

  • Inaccuracy: Using the percent symbol after the SELECT statement in SSMS can lead to inaccurate results if the data is not evenly distributed. This can result in data being omitted or included that does not meet the intended criteria. 
  • Ambiguity: Using the percent symbol after the SELECT statement in SSMS can result in ambiguous results if the data is not sorted properly. This can lead to confusion and errors in data analysis. 
  • Limited Control: Using the percent symbol after the SELECT statement in SSMS provides limited control over the data that is retrieved. This can be a disadvantage when working with complex or sensitive data that requires more precise filtering.