SQL Server Technologist

DATEADD  This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value. The DATEADD function simply allows you to add or subtract the specified number of units of time to a specified date/time value. Syntax DATEADD (datepart , number , date) Arguments datepart - The part of date to which DATEADD adds an integer number. This table lists all valid datepart arguments. datepart - Abbreviations year - yy, yyyy quarter - qq, q month - mm, m dayofyear - dy, y day - dd, d week - wk, ww weekday - dw, w hour - hh minute - mi, n second - ss, s millisecond - ms microsecond - mcs nanosecond - ns number - An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD will truncate a specified number value that has a decimal fraction. It will not round the number value in this situation. date - An expression that can resolve t...

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order. Syntax of all ways of using ORDER BY is shown below: Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. Syntax: SELECT * FROM table_name ORDER BY column_name ASC|DESC table_name: name of the table. column_name: name of the column according to which the data is needed to be arranged. ASC : to sort the data in ascending order. DESC : to sort the data in descending order. | : use either ASC or DESC to sort in ascending or descending order Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the nam...

Summary:  From this post you will learn how to use date and time  conversion   function. SQL Server provides a number of options you can use to format a date/time string. How to get different SQL Server date formats: Use the date format option along with CONVERT function. To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23). To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1). Check out the chart to get a list of all format options. Convert datetime to date using the CONVERT() function This statement uses the CONVERT() function to convert a datetime to a date: Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  expression  -  Any valid expression. data_type  - The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. length  - An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30. s...

Summary:  In this post, you will learn &  able to download  SSMS. About  SQL Server Management Studio ( SSMS ):  SSMS is Free!! SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts. Use SSMS to query, design and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.   SSMS -  Prerequisites for Installation: SSMS supports the following 64-bit platforms when used with the latest available service pack: Supported Operating Systems: Windows 10 (64-bit) version 1607 (10.0.14393) or later Windows 8.1 (64-bit) Windows Server 2019 (64-bit) Windows Server 2016 (64-bit) Windows Server 2012 R2 (64-bit) Window...

Summary:  From this post you will learn how to use  CURSOR  statement in SQL.  What is Database CURSOR:  A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time.  The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.  SQL Server cursors are used for Development, DBA and ETL processes. Why Use a Cursor in SQL Server:   Although using an INSERT , UPDATE or DELETE statement to modify all of the applicable data in one transaction is generally the best way to work with data in SQL Server, a cursor may be needed for: Iterating over data one row at a time. Completing a process in a serial manner such as SQL Server database backups. Updating data across numerous tables for a specific account. Correcting data with a predefined set of data as the input to the cursor. When to Use a SQL Server Cursor: The ...

WITH common_table_expression (Transact-SQL) Summary:  From this post you will learn about Common Table Expression or   CTE  in SQL Server using the with Clause.  About  CTE  Statement:  Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT , INSERT , UPDATE , DELETE or MERGE statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.  Syntax: [ WITH <common_table_expression> [ ,...n ] ]   <common_table_expression>::=       expression_name [ ( column_name [ ,...n ] ) ]       AS       ( CTE_query_definition )   Arguments expression_name Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table...

Summary:  From this post you will learn about  CREATE TABLE  statement in SQL.  About  CREATE TABLE  Statement:  Creates a new table in SQL Server and Azure SQL Database. There are two types of tables i.e. Constant and Temporary Table. Syntax for  Constant  Table : CREATE TABLE     { database_name.schema_name.table_name | schema_name.table_name | table_name }     ( { <column_definition> } [ ,...n ] ) [ ; ] Arguments: database_name Is the name of the database in which the table is created. database_name must specify the name of an existing database. If not specified, database_name defaults to the current database. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have CREATE TABLE permissions. schema_name Is the name of the schema to which the new table belongs. table_name Is the name of the new table. Table names must ...

Summary:  From this post you will learn how & when to use DELETE statement in SQL.  About DELETE Statement:  Removes one or more rows from a table or view in SQL Server.  DELETE  statement can be used broadly in 6 categories to perform row deletion. 1. Using DELETE to discard entire rows in a table: The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because a WHERE clause is not used to limit the number of rows deleted DELETE FROM Sales.SalesPersonQuotaHistory;   GO  2. Using the WHERE clause to delete a set of rows i.e. limit the number of rows: The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 5000.00. DELETE FROM Production.ProductCostHistory  WHERE StandardCost > 5000.00;   GO    3. Using a cursor to determine the row to delete The foll...