This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Do you know any type of mathematical function?
- CEILING():Returns the value representing the smallest integer that is greater-than or equal to the input_number.
- FLOOR ():Returns the value that represents the largest integer that is less-than or equal to the input_number.
- ROUND():Rounds a value to the number of decimal places specified in the second parameter.
- ABS():Returns the absolute value of any number passed to it.
- POWER(X,Y) :Returns a number(x) raised to the power of a number (y).
- SQUARE():Returns the square of the given number
What are the most important aggregate functions, which we are using in day to day life?
- AVG() : Returns average of given column; The column must contain numeric values.
- MAX() : Returns Max-value from given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
- MIN() : Returns Mininimum value from the given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
- SUM() : Returns Sum of values avialable in a given column. The column must contain numeric values.
- COUNT() : Returns No. of rows or values in the given column; The only aggregate function not being applied to column is COUNT(*).
What is the difference between COUNT(1) and COUNT(*)?
It is a very common perception that the Count(1) performs better compared to Count(*), however it is not the case. If you test by looking at the execution plan, you will see the same action being performed by both the commands and same number of rows being scanned. The time taken may be slightly different in terms of CPU usage for count(*) , but is almost same as count(1).
We are calling some function using @@, what is this?
- These are special types of functions that does not take any parameters and returns a single value
- These are invocable as @@function name;
- These are also called Global Variables.
What are the most important Global variables or Scalar function, which we are using in day to day life?
@@VERSION:
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
Example: SELECT @@Version
@@LANGUAGUE:
Returns the name of the language currently being used.
Example: Select @@Language
@@servername:
Returns the name of the local server that is running SQL Server.
Example: Select @@Servername
@@Max_connection:
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server.
Example: Select @@Max_Connection
Which are the different functions available to work with NULL values?
ISNULL(): determines whether or not it is null.
Syntax: ISNULL(check_expression, replacement_value)
NULLIF(): Returns NULL if both values are equal, otherwise returns the first expression.
Syntax: NULLIF(exp1,exp2);
COALESCE(): Getting first not null value from list.; returns the first Non-NULL value in a list of expressions. It is equivalent to CASE expression but works with NULL values.
Syntax: COALESCE(expression1, expression2, … expressionN)
What are the different function to SELECT current date and time?
- CURRENT_TIMESTAMP: Returns current date and time.
- GETDATE(): Returns the current system date and time.
- GETUTCDATE(): Returns GMT(Greenwich Mean Time) or Universal Time Coordinate(UTC) time.
How you can find the difference of date in the day format?
- DATEDIFF(): It subtracts the first date from the second date.
- Syntax: DATEDIFF(Datepart , Startdate , Enddate)
- Using Day interval: SELECT (day,’2016-04-16′,’2016-05-10′)
- Using Month interval: SELECT (month,’2015-01-26′, ‘2016-05-16’)
- Using Year interval: SELECT (year,’2012-01-02′,’2016-05-16′)
What is the use DATEADD()?
It adds or subtracts a number of days, months, or years from a specific date.
SYNTAX: DATEADD(date_part, number, date)
Example: Select DATEADD(day,8, GETDATE())
When should I use SPARSE COLUMN?
- Storing a null in a sparse column takes up no space at all.
- To any external application the column will behave the same
- Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
- You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.
- Change Data Capture and Transactional replication both work, but not the column sets feature.
What are the disadvantages of SPARSE COLUMN?
- SPARSE column does not have IDENTITY or ROWGUIDCOL property.
- SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
- SPARSE column can not have default value or rule or computed column.
- Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
- Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes. A table operation which involves SPARSE column takes performance hit over regular column.
What is the Constraint in SQL Server?
Constraint let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.
What do you know about the CHECK constraint?
CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.
For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.
What is the difference between column level and table level constraint?
Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. The constraints in the previous examples are column constraints. A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint.
For example, if a table has two or more columns as a composite primary key, you must use a table constraint to include both columns in the primary key.
How to find open transactions information in SQL Server?
What do you please write a script to list all Dates between two dates?
What do you please write sample script to delete all duplicates rows except one?
