Skip to main content

Posts

Showing posts from June, 2021

How to format query results to JSON?

By using FOR JSON clause we can simply format the query results to JSON output.  Let's look into an example, SELECT Id, ProductNo, ProductName from tbl_products FOR JSON AUTO; In the above statement, we have three columns (Id, ProductNo, ProductName) from the Products table with FOR JSON clause and AUTO mode to automatically return the JSON formatted output based on the order of SELECT statement. Output [ { "Id":1, "ProductNo":2, "ProductName":"Alexa Eco Dot" }, { "Id":2, "ProductNo":3, "ProductName":"Alexa Eco Dot1" }, { "Id":3, "ProductNo":4, "ProductName":"Alexa Eco Dot2" }, { "Id":4, "ProductNo":5, "ProductName":"Alexa Eco Dot3" } ]

How to create a sequence in SQL?

Sequence is a unique set of numerical values 1, 2, 3, ... which are generated based on needs.  CREATE SEQUENCE sequence_name START WITH integer_value INCREMENT BY integer_value MINVALUE minimum_value MAXVALUE maximum_value CYCLE|NOCYCLE sequence_name : Unique name of the sequence. minimum_value: Minimum value of the sequence. maximum_value: Maximum value of the sequence. cycle: It continues the same sequence once it reaches the maximum value. Basically, once the maximum value is reached it resets the next value as minimum value. nocycle: Throws an error message when it reaches the maximum value. Example Let's create a sequence named "prod_sequence" like mentioned in below statement, CREATE SEQUENCE prod_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 NO CYCLE After the sequence has been created to see the properties of the sequence use below statement, SELECT * FROM sys.sequences WHERE name = 'prod_sequence'; Using Sequence The below...