Get Current time zone time in SQL

Hi,

Below query will give the time according to time zone

DECLARE @TIMZONE VARCHAR(6)

DECLARE @USERTIMZONE VARCHAR(50) = 'HAWAIIAN STANDARD TIME'

SELECT @TIMZONE = CASE @USERTIMZONE

WHEN 'CENTRAL STANDARD TIME' THEN '-06:00'

WHEN 'EASTERN STANDARD TIME' THEN '-05:00'

WHEN 'HAWAIIAN STANDARD TIME' THEN '-10:00'

WHEN 'MOUNTAIN STANDARD TIME' THEN '-07:00'

WHEN 'PACIFIC STANDARD TIME' THEN '-08:00' END

SELECT CAST(SWITCHOFFSET(TODATETIMEOFFSET(GETUTCDATE(), '+00:00'),@TIMZONE) AS DATE)

Thanks,

RS

Definition and information of sql object

Hi,

Below are the three ways by which we can get the definition and information of sql object like view, procedure or function

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound

FROM sys.sql_modules

WHERE object_id = OBJECT_ID('<storedProcedure_Name>');

SELECT OBJECT_DEFINITION (OBJECT_ID('<storedProcedure_Name>')) AS ObjectDefinition;

EXEC sp_helptext '<storedProcedure_Name>'

Thanks,

RS

CONCAT_NULL_YIELDS_NULL Options

Hi,

What is the result of below query?

SELECT 'ABC' + NULL

Ans: Null

Now If I want this result as 'ABC' then what I have to do.

In this case we can use CONCAT_NULL_YIELDS_NULL options in sql.

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT 'ABC' + NULL

Now the above query will give us the result 'ABC'

Below are the values for CONCAT_NULL_YIELDS_NULL options

Required value : ON

Default server value : ON

Default OLE DB and ODBC value : ON

Default DB-Library value : OFF

Thanks,

RS

SQL-Server: modify, exist in xml

Hi,

In sql-server we have modify,exist and delete functions to change the xml data. Below is the example of replacing the xml value when it is having some specified value.

Example: I want to change the Node A value only when it is having 1 as a value 

declare @tbl table(col xml)

declare @sql xml ='<SomeNode><A>1</A><B>1</B></SomeNode><SomeNode><A /><B /></SomeNode><SomeNode><A /><B /></SomeNode>'

insert into @tbl

select @sql

select * from @tbl

update @tbl

set    col.modify('replace value of (/SomeNode/A[. = "1"]/text())[1] 

                       with ""')

where  col.exist('/SomeNode[A = "1"]') = 1 and

       col.exist('/SomeNode[A = "1"]') = 1

select * from @tbl

Thx,

RS

11/12/13

Sorting comma separated values

Hi,

Below code will sort the comma separated values.

for using this code we need to use fnSplit method.

DECLARE @list VARCHAR(100)

set @list = '11,25,20,95,42,35,61,70,101,95,96,28';

WITH ComaSeparated AS

(

SELECT  *

FROM    dbo.fnSplit(@list,',')

)

SELECT TOP 1 SUBSTRING((SELECT ',' + item 

FROM ComaSeparated t2 

ORDER BY CAST(item AS INT) 

FOR XML PATH('')),2,LEN(@list)+1) FROM ComaSeparated

Helpful Queries

Hi,

There are some queries which will be help full many times 

Below query will give the list of dependent objects on a table 

SELECT DISTINCT

SYSOBJECTS.NAME 'TABLE NAME',

PROCEDURES.NAME 'STORED PROCEDURE'

FROM SYSOBJECTS

JOIN (SYSOBJECTS PROCEDURES

JOIN SYSDEPENDS

ON PROCEDURES.ID = SYSDEPENDS.ID)

ON SYSDEPENDS.DEPID = SYSOBJECTS.ID

WHERE SYSOBJECTS.NAME = 'TABLE NAME'

Below query will help to find the which record is locked

        

SELECT [Table Name] FROM [Table Name] WITH(NOLOCK)

WHERE ID NOT IN

(

    SELECT ID FROM [Table Name] WITH(READPAST)

)

Below query will help to find who is looged in into database

          

SELECT host_name,session_id,login_time

FROM sys.dm_exec_sessions a

inner join sysdatabases b on a.database_id = b.dbid

where name = 'database name'

Below query will help to find in how many tables particular column is used.

     

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

 WHERE c.name LIKE '%ColumnName%' and SCHEMA_NAME(schema_id) = 'Schema Name' ORDER BY schema_name, table_name;

Below query will give us all the database name, creation date and file path in SQL-Server.

     

SELECT

name AS DBName,

crdate AS [Creation Date],

[filename] AS [File Path]

FROM

SYSDATABASES

Querying Microsoft SQL Server 2012 - 70461 Study Material

Create database objects (24%)

Create and alter tables using T-SQL syntax (simple statements)

• Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE

Create and alter views (simple statements)

• Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP

Design views

• Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications

Create and modify constraints (simple statements)

• Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints

Create and alter DML triggers.

• Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Preparation resources

Tables

Views

table_constraint(Transact-SQL)

CREATE TRIGGER(Transact-SQL)

Work with data (27%)

Query data by using SELECT statements

• Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce

Implement sub-queries

• Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement

Implement data types

• Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns

Implement aggregate queries

• New analytic functions; grouping sets; spatial aggregates; apply ranking functions

Query and manage XML data

• Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing

Preparation resources

SELECT(Transact-SQL)

INTO Clause(Transact-SQL)

FROM(Transact-SQL)

WITHcommon_table_expression (Transact-SQL)

Data Types(Transact-SQL)

FOR Clause(Transact-SQL)

Ranking Functions(Transact-SQL)

SubqueryFundamentals

XML Data (SQLServer)

Modify data (24%)

Create and alter stored procedures (simple statements)

• Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL

Modify data by using INSERT, UPDATE, and DELETE statements

• Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

Combine datasets

• Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements

Work with functions

• Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Preparation resources

CREATE PROCEDURE(Transact-SQL)

ALTER PROCEDURE(Transact-SQL)

INSERT(Transact-SQL)

UPDATE(Transact-SQL)

DELETE(Transact-SQL)

UNION(Transact-SQL)

MERGE(Transact-SQL)

User-DefinedFunctions

Troubleshoot and optimize (25%)

Optimize queries

• Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in

Manage transactions

• Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount

Evaluate the use of row-based operations vs. set-based operations

• When to use cursors; impact of scalar UDFs; combine multiple DML operations

Implement error handling

• Implement try/catch/throw; use set based rather than row based logic; transaction management

Preparation resources

TransactionStatements (Transact-SQL)

SET TRANSACTIONISOLATION LEVEL (Transact-SQL)

Cursors

TRY...CATCH(Transact-SQL)