Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Uses of SQL Desc

In this article we will discuss one of the most important functions in the SQL world. The DESC is known as the description in database system. It is used to know the description of database components.

DESC has two options. It is good to explain a diagram as healthy as to get back rows as of tuple in downward order.

The query SELECT * FROM EMPMST ORDER BY EFULLNAME DESC determines the yield sorted on EFULLNAME in downward arrangement.

DESC [RIBE] (SQL*Plus command):

Explain an SQL Table, View, Synonym, package or Function.

Sentence structure:

 DESC table
   DESC view
   DESC synonym
   DESC function
   DESC package

In different databases, I can illustrate each process e.g. desc BNP_UTILITY.GET_PARAM_VALUE. It is too likely to explain substance in one more diagram or by means of a database connection e.g. DESCRIBE user.table@db_link .

Data Types:

The explanation for processes and functions contain the kind of PL/SQL article (purpose or process) the forename of the function or procedure, the type of importance generated (for functions) the squabble name, types, whether contribution or production, and evasion principles, if any.

DESC user.object_name will for all time recognize a dissimilar database entity since a user's database substance has to contain sole names. E.g. you cannot make a FUNCTION with the similar forename as a TUPLE in the similar plan.

Data Dictionary:

A substitute in the direction of the DESC control is selecting based on the information lexicon:

DESC MY_TBLE

Is equivalent to:

Listing 1: Sample showing execution plan

  
SELECT 
column_name "Nme", 
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE tuple_nme=TUPLE_NME_TO_explain;

Column Comments:

To view column comments:

Listing 2: Sample showing select statement

 
SELECT comments
FROM user_col_comments 
WHERE table_name='MY_TBLE';

The writing underneath determine assist out:

COL.SQL
-- List all the columns of a table.

Listing 3: Sample showing select execution

 
select chr(9)||lower(column_nme)||',' 
from USR_tab_column 
where table_Name = UPPER('&1') 

How can we use terminology in the ORDER BY phrase?

Expressions in the ORDER BY clause of a SELECT statement

For example: If I wish to exhibit employee name, salary, and a 30% increase in the salary for merely individual employees for whom the proportion enlarge in salary is greater than 40000 and in descending order of the greater than before price, the SELECT declaration can be printed as revealed underneath.

Listing 4: Sample showing execution plan for the select query

 
SELECT name, salar, salar*1.2 AS new_salary 
FROM employeemst 
WHERE salar*1.2 > 30000 
ORDER BY new_salary DESC; 

The result is:

name salary new_salary
Abhijit 32500 39000
Maniraj 35000 42000
Raktim 32500 39000

NOTE: Aliases define within the SELECT Statement is able to live employ in ORDER BY Clause.

Through evasion, the ORDER BY article sort information in ascending order. If I wish to arrange the information in downward order, you have to clearly identify it as revealed underneath.

Listing 5: Sample showing execution plan for order by

 
SELECT name, salar 
FROM employeemst 
ORDER BY name, salar DESC;

The on top of reservation sort merely the article 'salary' in downward order and the column 'name' by upward order.

If I wish to choose equally name and salary in downward order, the inquiry would be as specified underneath.

Listing 6: Sample showing execution plan for order by with two options

 
SELECT name, salar 
FROM employeemst 
ORDER BY name DESC, salar DESC; 

DESC MY_TABLE is equivalent to:

Listing 7: Sample showing execution plan with where clause

 
SELECT column_name "Nme", nullable "Null?", concat(concat(concat(data_type,'('),data_length),')') "Type" FROM usr_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';

I have approximately translate so as to to the SQL Server corresponding intended for you - immediately create certain you are management it on top of the EX database.

Listing 8: Sample showing execution plan with different clauses

 
SELECT column_name AS [Nme],
       IS_NULLABLE AS [Null?],
       DATA_TYPE + '(' + CASEWHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
                           ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                         END + ')' AS [Type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'EMP_MST'
Order_by_expression 

The above specifies an article or looks on which kind the inquiry consequence took place. A class article is able to exist in particular since a name or article alias, or a nonnegative numeral on behalf of the place of the article in the chosen catalog.

Numerous kinds of columns are able to take part in the query. Article name have to be only one of its kind. The progression of the genus column in the ORDER BY phrase defines the association of the sorted consequence. The product set is sort by the first article and after that prearranged catalog is sort by the next article, and consequently on.

The tuple names referenced in the ORDER BY phrase must communicate to moreover a feature in the chosen list or to a feature distinct in precise in the FROM clause with no ambiguity.

ASC | DESC Specifies to the principles in the particular article be supposed to be sorted in climbing or downward order. ASC sorts on or after the lowly worth to uppermost value. And DESC sort from uppermost worth to lowly value. ASC is the evasion sort arrange. Null principles are treated as the lowest likely principles.ROW and ROWS are synonyms and are provide for ANSI compatibility. In query execution plans, the offset row count value is displayed in the Rows or Top attribute of the TOP query operator.

Please be away from specifying integers in the ORDER BY phrase as positional representation of the column in the chosen catalog.

Product ORDER BY 2 is suitable; the declaration is not as with no trouble unstated by others compare with specifying the definite article name. In addition, changes to the chosen catalog, such as altering the article arrange or addition new columns, will need to modify the ORDER BY section in arrange to keep away from unforeseen consequences.

Limitations and Restrictions:

There is no perimeter to the numeral of column in the ORDER BY section; on the other hand, the entire dimension of the column particular in an ORDER BY section cannot exceed 9,060 bytes. Column of kind ntext, text, image, geography, geometry and xml cannot be used in an ORDER BY phrase.

If a bench person's name is aliased in the as of clause, merely the alias name can be worn to meet the criteria its column in the ORDER BY phrase.

SYNTAX:

SELECT [ ALL | DISTINCT ] 
[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
column1 AS displaynme1, displaynme2 = column_nme2, column_nme3 displaynme3 
[ INTO new_table ] 
[ FROM { table_source } [ ,...n ] ] 
[ WHERE search_condition AND search_condition ] 
[ GROUP BY ] 
[ HAVING search_condition ] 
[ ORDER BY order_expression [ ASC | DESC ] 

PURPOSE:

DESC gives the permission us to ORDER BY a column in descending order.

/* DESC example
 PURPOSE: DESC gives the permission us to ORDER BY a column in descending order 
          ASC (ascending is the by default if we do not state desc)
 
 
 SYNTAX: SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			< column1 AS displaynme1, displaynme2 = column_nme2, column_nme3 displayname3 > 
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE searching_cond AND searching_cond ] 
			[ GROUP BY ] 
			[ HAVING  searching_cond ] 
			[ ORDER BY  order_express [ ASC | DESC ] ] 
*/

How can we obtain the SQL Server error code/desc?

When SSIS correspondence job, they are immense other than at what time rows cannot be inserted keen on the target, I would akin to obtain the SQL Server mistake system and desc to comprise in a manuscript sleeve anywhere. I will be able to as well forward the chain.

Repeatedly entity SQL statement reason high rational and physical understand writing volume. At this time an inquiry to locate statement by means of the uppermost rational interpret volume.

Listing 9: Sample showing complete execution plan

 
select top(20) sum(Q.tot_physi_reads) as [Physi reds],
               sum(Q.tot_logi_reads) as [Logi reds],
               sum(Q.excution_count) as [Excution cont],
               min(coalesce(quotenme(db_name(Q.dbid)), N'-') + N'.' + coalesce(quotename(object_schema_nme(Q.objectid, Q.dbid)), N'-') + N'.' + coalesce(quotename(object_nme(Q.objectid, Q.dbid)), N'-')) as [Module],
               min(Q.statment_txt) as [Statement]
from (select QS.*,
             ST.dbid as dbid,
             ST.objid as objid,
             substring(ST.text,
                      (QS.statmnt_strt_counteract/2) + 1,
                      ((case statmnt_end_counteract 
                        when -1 then datalenth(ST.txt)
                        else QS.statmnt_end_ counteract end 
                        - QS.statmnt_start_ counteract)/2) + 1) as statement_text
      from sys.dm_exec_query_stats as QS
           cross apply sys.dm_exec_sql_txt(QS.sql_hndle) as ST) as Q
--where Q.dbid = db_id()
group by Q.query_hash
order by [Logical Reds] desc;

Statement physical reads:

Here is a question to locate statement with the uppermost physical read volumes.

Listing 10: Sample showing execution plan for physical read

 
select top(10) sum(Q.total_physi_reads) as [Physi reads],
               sum(Q.total_logi_reads) as [Logi reads],
               sum(Q.execution_cont) as [Execution cont],
               min(coalesce(quotenme(db_nme(Q.dbid)), N'-') + N'.' + coalesce(quotename(object_schema_nme(Q.objectid, Q.dbid)), N'-') + N'.' + coalesce(quotename(object_nme(Q.objectid, Q.dbid)), N'-')) as [Module],
               min(Q.statement_txt) as [Statement]
from (select QS.*,
             ST.dbid as dbid,
             ST.objid as objid,
             substring(ST.txt,
                      (QS.statmnt_start_ counteract /2) + 1,
                      ((case statmnt_end_ counteract
                        when -1 then datalenth(ST.txt)
                        else QS.statmnt_end_ counteract end 
                        - QS.statmnt_start_ counteract)/2) + 1) as statement_txt
      from sys.dm_exec_query_stats as QS
           cross apply sys.dm_exec_sql_txt(QS.sql_handle) as ST) as Q
--where Q.dbid = db_id()
group by Q.query_hash
order by [Physi Reds] desc;

Conclusion:

The ORDER BY reserved word sorts the proceedings in ascending order by evasion. To arrange the records in a descending order, we can employ the DESC reserved word. It is not desirable to employ ORDER BY DESC in Views. Use ORDER BY DESC in the exterior part of the views. In detail, the right plan will entail the similar. If we employ TOP the length of by means of Views, there is a high possibility so as to View will not give all the rows of the tuple or will pay no attention to ORDER BY DESC entirely.

I hope you have understood the concepts and use it on your SQL development. Enjoy reading.

See also



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login