Monday, September 29, 2014

Create SQL Login

<![if !supportLists]>1.       <![endif]>CREATE a login for SQL Server  for windows authentication

      CREATE LOGIN [domainName\loginName] FROM WINDOWS;    
                                                     
<![if !supportLists]>2.       <![endif]>CREATE a login with password –( SQL Login) this will work from SQL 2000 onwards

CREATE LOGIN VenkatSangu WITH PASSWORD = 'password'

<![if !supportLists]>3.  <![endif]> CREATE a login with password and password must change when the user first time connecting to SQL Server  this will not support in older version i.e SQL Server 2000/2005

CREATE LOGIN VenkatSangu WITH PASSWORD='pwd@123pdw' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON
     
To use MUST_CHANGE keyword must passwords must meet the following guidelines
<![if !supportLists]>·         <![endif]>The password does not contain the account name of the user.
<![if !supportLists]>·         <![endif]>The password is at least eight characters long.
<![if !supportLists]>·         <![endif]>The password contains characters from three of the following four categories:
<![if !supportLists]>·         <![endif]>Latin uppercase letters (A through Z)
<![if !supportLists]>·         <![endif]>Latin lowercase letters (a through z)
<![if !supportLists]>·         <![endif]>Base 10 digits (0 through 9)
<![if !supportLists]>·         <![endif]>Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).


Thanks
Venkat Sangu

Friday, September 26, 2014

Recently executed query




Using below query you can get executed time and query text
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Results of above query



Using below query you can find the Recently excuted query for specific SPID
SELECT SDEC.[most_recent_sql_handle], DEST.[text], last_read, Last_write
FROM sys.[dm_exec_connections] SDEC
 CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
WHERE SDEC.[most_recent_session_id] = 52

Results of above query

Thanks
Venkat Sangu

Thursday, September 18, 2014

SQL & PDW

SQL & PDW

 

·        Functions will not work in PDW Environment

·        Foreign keys does not support in PDW

·        Datatypes

o   No BLOBs

o   No CLR Types

o   No Numeric

o   Numeric replaced with decimal

·        In PDW you can’t use update/delete statements using joins.

·        You can’t do update statistics automatically.