Wednesday, March 13, 2013

Operators in SQL Server


Scope Resolution Operator: 
The scope resolution operator :: provides access to static members of a compound data type.
A compound data type is one that contains multiple simple data types and methods.


DECLARE @hid hierarchyid;
SELECT @hid = hierarchyid::GetRoot();
PRINT @hid.ToString();


Result: /

Bitwise Operators:

DECLARE @a int;
SET @a = 10 & 20
print @a

SET @a = 10 ^ 20
print @a

SET @a = 10 | 20

print @a

Result:
0
30
30
Explanation:

Binary Representation of 10, 20:

10 = 01010
20 = 10100

01010 & 10100 = 00000 = 0
01010 ^ 10100 = 11110 = 30
01010 | 10100 = 11110 = 30


 

Concatenating two strings:

SELECT 'FirstName' + ' ' + 'LastName' AS FullName

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

SELECT FirstName + ' ' + LastName
FROM Person.Person

Wildcards:
% - Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Dan%';
           
[] - Matches any single character within the specified range or set that is specified between the brackets.

SELECT
      e.BusinessEntityID,
      p.FirstName,
      p.LastName,
      a.PostalCode
FROM HumanResources.Employee AS e
      INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress AS ea ON e.BusinessEntityID = ea.BusinessEntityID
      INNER JOIN Person.Address AS a ON a.AddressID = ea.AddressID
WHERE a.PostalCode LIKE '[0-9][0-9][0-9][0-9]';

[^] - Matches any single character that is not within the range or set specified between the square brackets.

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Al[^a]%'

ORDER BY FirstName;

 

_ - Matches any single character in a string comparison operation that involves pattern matching, such as LIKE and PATINDEX.

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE '_an'

ORDER BY FirstName;

 


Tuesday, March 12, 2013

SQL Queries to check configurations in SQL Server



SQL Queries to check different server Properties:

1.    Max Server Memory:

Queries:

SELECT * FROM sys.Configurations WHERE
NAME = 'max server memory (MB)'

SELECT * FROM sys.dm_os_sys_memory


Update Query for Max Server Memory:

Exec sp_configure 'max server memory (MB)', 12228
Reconfigure


2.    Backup Compression Default:
Default Value: 0
Updated Value as per standards: 1

Queries:
SELECT * FROM sys.Configurations WHERE
NAME = 'backup compression default'


How to update Backup Compression:
Exec sp_configure 'backup compression default', 1



3.    Instant Data File Initialization:
Default Value: Off
Standards: On

4.    MAXDOP
Default Value: 0
Standards: n (If there are n processors)

Queries:

exec master..xp_msver processorCount
Exec sp_configure 'max degree of parallelism', 4


5.    How to check Authentication mode:
SELECT
            CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
                        WHEN 1 THEN 'Windows Authentication'  
                        WHEN 0 THEN 'Windows and SQL Server Authentication'  
            END as [Authentication Mode] 


6.    How to check TempDB Properties:

SELECT
    name AS FileName,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;