BE AWARE FOLLOWING THING WHILE WRITING SQL QUERY

AS SALAMO ALAIKUM WA RAHMATULLAH

Queries

Writing efficient queries in SQL Server is more an exercise in writing elegant relational queries than in knowing specific tricks and syntax tips. Generally, a well-written, relationally correct query written against a well-designed relationally correct database model that uses the correct indexes produces a system that performs fairly well and that is scalable. The following guidelines may help you create efficient queries:

• Know the performance and scalability characteristics of queries.
• Write correctly formed queries.
• Return only the rows and columns needed.
• Avoid expensive operators such as NOT LIKE.
• Avoid explicit or implicit functions in WHERE clauses.
• Use locking and isolation level hints to minimize locking.
• Use stored procedures or parameterized queries.
• Minimize cursor use.
• Avoid long actions in triggers.
• Use temporary tables and table variables appropriately.
• Limit query and index hints use.
• Fully qualify database objects

--
MA ASALAAM
PASSION 4 ORACE

FULLBACK DIFFERENTIAL BACKUP AND TRANSACTION LOG BACKUP WITH EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH

USE  AdventureWorks2012

Go

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\ DB_Backup_Daily\DifferentialBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.bak';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP DATABASE AdventureWorks2012 TO DISK = @FinalPath WITH DIFFERENTIAL

End

USE AdventureWorks2012

GO

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\DB_Backup_Daily\FullBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.bak';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP DATABASE AdventureWorks2012 TO DISK = @FinalPath WITH INIT

End

USE FahaheelLiveStore

go

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\DB_Backup_Daily\TransactionBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.log';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP LOG AdventureWorks2012 TO  DISK = @FinalPath WITH STATS =1

END

--
MA ASALAAM
PASSION 4 ORACLE

DBCC COMMAND WITH EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH
SQL Server – DBCC Commands
DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005  on wards, with examples.
The DBCC Commands broadly falls into four categories:
Ø  Maintenance
Ø  Informational
Ø  Validation
Ø  Miscellaneous
v  Maintenance Commands
Performs maintenance tasks on a database, index, or filegroup.
1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE (‘AdventureWorks’,'Person.Contact’,0)
2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)
USE AdventureWorks
DBCC DBREINDEX (‘Person.Contact’,'PK_Contact_ContactID’,80)
3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
DBCC DROPCLEANBUFFERS
4. FREEPROCCACHE – Removes all elements from the procedure cache
DBCC FREEPROCCACHE
5. INDEXDEFRAG – Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)
6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)
7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.
USE AdventureWorks;
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)
8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks)Informational Commands
v  Informational Commands
              Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.
DBCC CONCURRENCYVIOLATION
2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
DBCC INPUTBUFFER (52)
3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.
DBCC OPENTRAN;
4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
DBCC OUTPUTBUFFER (52)
5. PROCCACHE – Displays information in a table format about the procedure cache.
DBCC PROCCACHE
6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table
USE AdventureWorks
DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)
7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG (‘HumanResources.Employee’);
8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
DBCC SQLPERF(LOGSPACE)
9. TRACESTATUS – Displays the status of trace flags.
DBCC TRACESTATUS(-1)
10. USEROPTIONS – Returns the SET options active (set) for the current connection.
DBCC USEROPTIONS
v  Validation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)
2. CHECKCATALOG – Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)
3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)
5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP
6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT (‘HumanResources.Employee’)
7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE (‘HumanResources.Employee’)
v  Miscellaneous Commands
Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)
2. TRACEOFF – Disables the specified trace flags.
DBCC TRACEOFF (3205)
3. HELP – Returns syntax information for the specified DBCC command.
– List all the DBCC commands
DBCC HELP (‘?’)
– Show the Syntax for a given DBCC commnad
DBCC HELP (‘checkcatalog’)
4. TRACEON – Enables the specified trace flags.

DBCC TRACEON (3205)
--
MA ASALAAM
PASSION 4 ORACLE

WHAT IS DMF AND DMV IN SQL SERVER?

AS SALAMO ALAIKUM WA RAHMATULLAH

There were many new DMVs added in SQL Server 2012, and some that have changed since SQL Server 2008 R2. This is a brief overview of the biggest changes and what they mean to you.
Introduction
Dynamic management views (DMVs) are immensely useful tools that are provided with all editions of SQL Server since 2005. Using them, you can quickly find out information about many aspects of SQL Server, everything from index usage, query cache contents, server process data, wait stats, and much, much more.
With each new version of SQL Server comes many new DMVs as well as changes to existing ones. What follows are the highlights of DMV changes in SQL Server 2012.  In addition, I have provided a spreadsheet with a list of all changes that I could identify between SQL Server 2008 R2 and SQL Server 2012. If you are upgrading, this list could be very useful to ensure that data collectors, debugging stored procedures, or other TSQL that you have written are modified as necessary to keep working, and that new views can be utilized to fill any existing gaps in monitoring.
The Details A list of DMVs can be generated in any version of SQL Server using the following query:
SELECT *FROM sys.system_objects
WHERE name LIKE 'dm_%'
AND type = 'V'
ORDER BY name
With no further delay, here are some of the more significant DMV changes in SQL Server 2012:
sys.dm_server_services
This contains details on each service that is running on your SQL Server, including the startup type, status, process ID, service account, startup command, and some cluster details. Being able to quickly return this data using T-SQL can be a big time saver, and could allow you to do some rudimentary monitoring on the service, or verify that all of its settings are what they should be. Here is a quick select of the contents of this DMV on my local test server.


Note that this DMV was available in SQL Server 2008R2 starting in service pack 1.
sys.dm_os_windows_info
This straightforward one-line DMV contains some basic version info on the OS that is running your SQL Server:

Note that this DMV was also available in SQL Server 2008R2 starting in service pack 1.
sys.dm_db_log_space_usage
This is another super-simple view that provides the size of your transaction log and the space used:

sys.dm_server_registry
Quick access to registry settings for your SQL server can be found right here!  This DMV can be invaluable for troubleshooting a settings issue or confirming that SQL Server is configured correctly:

Note that this DMV was also available in SQL Server 2008R2 starting in service pack 1.
sys.dm_db_uncontained_entities
If an entity in a database references anything outside of the current database, it is considered an uncontained entity and will be reported in this DMV. This can be especially handy if you are renaming, moving, or otherwise making changes to an object that could be referenced by another database. This view can be used to check for these unenforced dependencies and prepare changes as needed. The example below creates an entity such as this as an example:
CREATE DATABASE uncontained_entity_test
GO
USE uncontained_entity_test
GO

CREATE PROCEDURE uncontained_entity_test_proc AS
       SELECT
              *
       FROM master.sys.dm_os_wait_stats;
GO

SELECT
       SYS_SCHEMAS.name AS schemaname,
       SYS_OBJECTS.name AS objectname,
       SYS_OBJECTS.type_desc,
       SYS_OBJECTS.create_date,
       SYS_OBJECTS.modify_date,
       ENTITIES.class_desc,
       ENTITIES.statement_line_number,
       ENTITIES.statement_type,
       ENTITIES.feature_name,
       ENTITIES.feature_type_name
FROM sys.dm_db_uncontained_entities ENTITIES
INNER JOIN sys.objects SYS_OBJECTS
ON SYS_OBJECTS.object_id = ENTITIES.major_id
INNER JOIN sys.schemas SYS_SCHEMAS
ON SYS_SCHEMAS.schema_id = SYS_OBJECTS.schema_id
In this SQL we create a simple stored procedure that selects data from the master database, which is outside of the scope of our current database.  The query returns basic info on the stored proc, its schema, and the referenced object:


In this example, we can see our proc (dbo.uncontained_entity_test_proc) under objectname and the external resource (dm_os_wait_stats) we are referencing under feature_name.  As an added bonus, statement_line_number tells you on what line of the proc our reference occurs, which could be very handy when dealing with a large proc.
sys.dm_tcp_listener_states
This new DMV provides data on which TCP ports are being used by the TCP Listener, and does so for T-SQL, the Service Broker, and for mirroring.  This can be very handy for troubleshooting connectivity problems or verifying that a server is set up with the correct port & IP address.  A look at my local (and somewhat boring) server:

Note that the “::” is used for an IPv6 wildcard.  A separate row is returned if a listener has both an IPv4 and an IPv6 address.
sys.dm_server_memory_dumps
Another simple view with troubleshooting in mind.  It will return basic file data for any dump files that have been generated by SQL Server:

The file can be a minidump, full dump, or an all-thread dump.
sys.dm_os_cluster_properties
This DMV provides one row of data about the SQL Server cluster settings.  If your instance is stand-alone and not on a failover cluster, then no rows are returned.  This view is focused squarely on troubleshooting, and contains info on dump files, failure conditions, logging and the health check timeout.
sys.dm_os_server_diagnostics_log_configurations
One row is returned by this DMV, which contains the basic configuration info for the SQL Server failover cluster diagnostic log:

If you use AlwaysOn Availability Groups, then there are a dozen new DMVs that provide a plethora of information on the feature.  Since these views may not apply to everyone, I will leave out the details, but suffice it to say that troubleshooting AlwaysOn issues will be much easier with these than without:
• sys.dm_hadr_auto_page_repair
• sys.dm_hadr_availability_group_states
• sys.dm_hadr_availability_replica_cluster_nodes
• sys.dm_hadr_availability_replica_cluster_states
• sys.dm_hadr_availability_replica_states
• sys.dm_hadr_cluster
• sys.dm_hadr_cluster_members
• sys.dm_hadr_cluster_networks
• sys.dm_hadr_database_replica_cluster_states
• sys.dm_hadr_database_replica_states
• sys.dm_hadr_instance_node_map
• sys.dm_hadr_name_id_map
Conclusion
The changes above outline only a fraction of the DMVs that have been added or altered in SQL Server 2012.  There is quite a bit of new functionality that is addressed in these new views.  Many existing views have had columns added, removed, or renamed.  In addition, other new views have been added to address new functionality in the Resource Governor, Full-Text Search, and other features in SQL Server.

For details of all major changes and additions, please see the attached document, which contains a list of DMVs in SQL Server 2008 R2 and SQL Server 2012.  A row exists for each instance of the DMV, one in 2008R2 and one in 2012, along with brief details on what was changed.

--
MA ASALAAM
PASSION 4 ORACLE