Top posts
-
Track the growth of a database
Frequently you want to know how fast your database has been growing. Based on the backup history we can get a rough outline of the growth of a database, over time, from the msdb..backupset table. This query will give the size of the backup, every time...
-
compound DML trigger in oracle
compound DML trigger rA compound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common...
-
plsql programing
What is PL/SQL? Procedural Language extension to SQL It integrates procedural constructs with SQL SET SERVEROUTPUT ON This command is used to send the output from the server to the screen Lesson 1-2 Introduction & Declaring PL/SQL Identifiers SQL> SET...
-
FILESTREAM (SQL Server)
FILESTREAM FILESTREAMenables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain...
-
SQL Server Features 2012
Features of SQL Server 2012 1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will...
-
different between index scan and index seek?
Index Scan When SQL Server does a scan it loads the object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs. Index Seek When SQL Server does a seek it knows where in...
-
How will you monitor replication activity and performance? What privilege do you need to use replication monitor
How will you monitor replication activity and performance? What privilege do you need to use replication monitor? The easiest way to monitor replication activity and performance is to use replicationmonitor.To monitor replication, a user must be a member...
-
What is “Push” and “Pull” subscription
What is “Push” and “Pull” subscription? Pull Subscription : In a pull subscription, changes to a subscriber cannot be publicized without any request from subscriber. This allows the user at the Subscriber to determine when the data changes are synchronized....
-
Backup All User Databases
Backup All User Databases If you want to backup only user databases and don’t want to include system databases then you can use below SQL script. DECLARE @DB VARCHAR(20) DECLARE @BkpFName VARCHAR(100) DECLARE @BkpFDate VARCHAR(50) SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50),...
-
DBCC COMMANDS WITH EXAMPLES
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...
-
Is it possible to run multiple publications and different type of publications from the same distribution database?
Is it possible to run multiple publications and different type of publications from the same distribution database? Yes this can be done and there are no restrictions on the number or types of publications that can use the same distribution database....
-
Is it possible to replicate data from SQL Server to Oracle
Is it possible to replicate data from SQL Server to Oracle? Yes this can be done using heterogeneous replication. In SQL Server 2000, publishing data to other databases such as DB2 or Oracle was supported; however, publishing data from other databases...
-
Data is not being delivered to Subscribers, what can be the possible reasons?
Data is not being delivered to Subscribers, what can be the possible reasons? There are a number of possible causes for data not being delivered to Subscribers: The table is filtered, and there are no changes to deliver to a given Subscriber. One or more...
-
AtomicityTransaction, Server Implicit,Auto commit, Explicit transaction
Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another ransaction What is Transaction Server Implicit? Implicit: when the transaction is in...
-
PL/SQL Explicit Cursor
PL/SQL Explicit Cursor Explicit Cursor which are construct/manage by user itself call explicit cursor. User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply...
-
What is full outer join? *
What is full outer join? * The Full Outer Join logical operator returns each row satisfying the join predicate from the first (top) input joined with each row from the second (bottom) input. It also returns rows from: The first input that had no matches...
-
PL/SQL TCL COMMANDS
PL/SQL Transaction You can use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION command to control the transaction. COMMIT : COMMIT command to make changes permanent save to a database during the current transaction. ROLLBACK : ROLLBACK command execute...
-
What is SQL Profiler?
What is SQL Profiler? SQL Profiler is a tool that captures SQL Server events from the server and saves those events in what's known as a trace file. You can then analyze or use the trace file to troubleshoot logic or performance problems. You can use...
-
When to use COALESCE() & ISNULL() Functions?
When to use COALESCE() & ISNULL() Functions? The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So...
-
What is Transaction Server Consistency?Server Isolation
What is Transaction Server Consistency? Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back. What is Transaction...
-
What are Editions of SQL Server 2012
What are Editions of SQL Server 2012 OS System requirements depend on the Version and Edition you are using. Enterprise and Datacenter Editions require Windows Server 2003 SP2 or Windows Server 2008 for complete features support. Core Server Editions...
-
SQL ORDER BY Clause
SQL ORDER BY Clause ORDER BY clause is used to sort the data in ascending or descending order, in SQL Server bydefault data will not be inserted in any order even if you have an index. Syntax: SELECT expressions FROM tables WHERE conditions ORDER BY expression...
-
SQL IN Operator
SQL IN Operator You can specify multiple values with IN operator and it will return matching values from WHERE clause of a table. IN operator matches values in a list or subquery and returns the output. Syntax SELECT column1, column2,… columnN FROM TableName...
-
SQL SERVER – Know Your Backup Before Deleting Databas
Backup Before Deleting Databas USE MSDB GO SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CASE msdb..backupset.TYPE WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential'...
-
See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery plan?
Q. See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery...