Table of Contents
Static Pages
Blog Posts
- MS SQL Server 2005/2008 (40)
- What if you forget the password of Database Master Key (DMK) by: Subhash
March 28, 2010, 07:35
Have you forgotten the Database Masker Key password of your database? If yes, then execute the below statement in your database as soon as possible. ALTER MAS…
- Database Master Key (DMK) by: Subhash
March 28, 2010, 07:34
One database can have only one Database Master Key at a time. Like Service Master Key, Database Master Key is also a symmetric key. Database Master Key is c…
- Service Master Key (SMK) by: Subhash
March 28, 2010, 07:32
Service Master Key is automatically generated the first time when it is needed. In most cases its created just after SQL Server installed. Because this key is n…
- Difference between Cell- level Encryption and Transparent Data Encryption (TDE) by: Subhash
March 28, 2010, 07:30
Cell- level Encryption Transparent Data Encryption (TDE) …
- Facts about SQL Server Data Encryption & Decryption by: Subhash
March 28, 2010, 07:24
1. Encryption is supported by SQL Server Developer and Enterprise editions only. 2. Encryption is the conversion of readable plaintext into ciphertext, which c…
- Using OUTPUT clause for auditing by: Subhash
March 28, 2010, 07:16
In SQL Server 2000 and earlier versions, trigger was used to log changes into a table for audit purpose. But the OUTPUT clause introduced in SQL Server 2005 can…
- Get free space in Data and Log files in all databases by: Subhash
March 6, 2010, 05:51
To get total size and free space (unallocated space) in transaction log files of all databases we have a very handy command: DBCC SQLPERF(LOGSPACE) …
- Best practices to write complex JOINs on multiple tables by: Subhash
March 5, 2010, 09:08
Always use ANSI standard joins: Tables can be joined in two join standards: ANSI and Transact-SQL. Syntax for these two standards is following: --Transact-SQL…
- The purpose of Transaction Log by: Subhash
March 4, 2010, 12:50
We all know that all DDL and DML changes are logged in Transaction log. But when I ask a question that what is the primary purpose of Transaction log in SQL Ser…
- Find who dropped, created or altered the table, SP or any object by: Subhash
February 24, 2010, 18:00
If any database object is dropped, created or altered accidently, sometimes you may need information about: Who dropped, created or altered the object?…
- Some unknown facts about Update (U) lock by: Subhash
February 22, 2010, 18:59
You may have read and found the following statement type – lock type relation easy to understand and logical too: SELECT – Shared (S) UPDATE – Update (U…
- The use of Synonyms by: Subhash
February 21, 2010, 10:39
We know synonym is an alternate name for an object. If you want to know more about synonym then please visit my blog : http://sqlreality.com/blog/ms-sql-server-…
- Synonyms in SQL Server by: Subhash
February 21, 2010, 05:03
Wikipedia says that “Synonyms are different words with identical or very similar meanings like student and pupil is synonym, buy and purchase is synonym.” …
- Life time (age) of Shared, Exclusive and Update lock by: Subhash
January 31, 2010, 16:24
SQL Server uses locks to maintain consistency while multiple transactions execute simultaneously. We know the various types of locks at various levels. But what…
- Index reduces blocking by: Subhash
January 31, 2010, 15:06
We know that indexes improve query performance by SEEKING the records instead of table SCAN. Because in case of index seek maximum number of comparisons is equa…
- Getting Deadlock details using Traces by: Subhash
January 31, 2010, 14:52
Deadlock is a circular locking among two or more processes that is automatically identified by SQL Server and resolved by killing one process. Whenever our proc…
- Know transaction log file size and free space for all databases by: Subhash
January 31, 2010, 11:16
The best way to get transaction log file’s total size and free space in percentage for all databases is using the old DBCC SQLPERF(LOGSPACE) command. &…
- Know failed and slow running jobs by: Subhash
January 29, 2010, 13:00
The below script returns two resultsets 1. Jobs that failed today. 2. Jobs that are running slow than average execution time. The second reultset have one col…
- In SQL Server 2008 SSMS run T-SQL code in debug mode by: Subhash
January 29, 2010, 12:50
SQL server 2008 reintroduced the T-SQL debugging feature in SSMS. Near the execute button a green Debug button is placed. In debug mode we can break the process…
- How to Add Watch in SQL Server 2008 SSMS debugging by: Subhash
January 29, 2010, 12:47
While debugging my code in SSMS I found the Watch window at Debug menu > Windows > Watch > Watch 1, 2, 3, 4. Now to monitor a variable’s value I w…
- Debugging asks to Configure Firewall for Remote Debugging by: Subhash
January 29, 2010, 12:46
I write some code in SSMS new query window and when to debug I clicked the green Debug button. A window asking to configure firewall to remote debugging appeare…
- Fixing error: The database principal owns a schema in the database, and cannot be dropped by: Subhash
January 29, 2010, 12:41
A user can be dropped using DROP USER command. But if that user owns any object that we get an error “The database principal owns a schema in the database, an…
- User – Schema relation by: Subhash
January 29, 2010, 12:37
We know that database objects are contained in Schema and schema are owned by Database Principals. But this is not all about this user-schema relation. Let u…
- Why the qualified naming is important by: Subhash
January 29, 2010, 12:19
When we call an object without specifying the schema (unqualified name), SQL Server searches it in following sequence: 1. In sys schema 2. In users default …
- Owner does not have permission by: Subhash
January 29, 2010, 12:18
In the below script I creates a user “UserA” and then. UserA creates a table and then execute a SELECT statement on his table. The script to perform this ta…
- Ownership chaining by: Subhash
January 29, 2010, 12:15
When a user executes a stored procedure, does permission for each object (table, view or function) called inside the stored procedure is checked? Answer is No…
- Troubleshooting the full transaction log problem by: Subhash
January 29, 2010, 08:55
One my database’s log file is increasing continuously. Database size is 5 GB while log file has exceeded 50 GB. Now to resolve the issue the first thing I nee…
- All possible RESTORE sequence by: Subhash
January 29, 2010, 08:49
If for a database the backup sequence is as below: FULL BACKUP FULL1 TRAN LOG BACKUP TLOG1 FULL BACKUP FULL2 TRAN LO…
- COPY_ONLY Backup by: Subhash
January 29, 2010, 08:48
We can take three types of backup of a SQL Server database that are full, differential and transactional. To successfully take a backup following are few points…
- ROLLBACK TRANSACTION behavior by: Subhash
January 29, 2010, 08:39
We have four statements to wok with transaction: 1. BEGIN TRANSACTION 2. COMMIT TRANSACTION 3. SAVE TRANSACTION 4. ROLLBACK TRANSACTION BEGIN TRANSAC…
- Collation precedence rules by: Subhash
January 29, 2010, 08:25
Following are collation precedence rules: 1. Any literal, variable, parameter or other string would have the collation of database. This is called Coercible-de…
- How to fix Collation conflict error by: Subhash
January 29, 2010, 08:23
During comparison or join of columns, collation conflict error occurs in two cases if collation of one column does not match with collation of another column. …
- Collation levels and default collation by: Subhash
January 29, 2010, 08:11
Collation can be set at 4 levels in SQL Server: 1. Server 2. Database 3. &…
- What is Collation in SQL Server by: Subhash
January 29, 2010, 08:08
Along with data type and length, textual data (char, varchar, nchar, nvarchar, text, ntext only) also have two more properties: Code page and Sort order. Cod…
- Database Compatibility Level: by: Subhash
January 28, 2010, 12:28
Whenever a database from one version of SQL Server is restored in newer version of SQL Server then do it started using all new features of newer version? Answ…
- Executing a SQL statement from command prompt: by: Subhash
January 28, 2010, 12:20
Sometimes we need to execute some –tsql script from command prompt. This is a common requirement when some t-sql scripts are needed to be scheduled by windows…
- Forced Parameterization by: Subhash
January 28, 2010, 11:15
If large numbers of ad hoc statement to your database are executed then performance of your server may degrade because of excessive recompilation. And such reco…
- How to increase the number of SQL Server log files by: Subhash
January 28, 2010, 06:42
When ever SQL Server restart or we add a new log file using sp_cycle_errorlog, a new log file is created. SQL Server by default allows 6 log files. After that w…
- SQL Server error log is too big by: Subhash
January 28, 2010, 06:39
Every time when SQL server starts a new error log file is added a Current log file. But if server is running from long time the size of current log file increas…
- Difference between compilation and recompilation by: Subhash
January 28, 2010, 06:25
While understanding the execution t-sql statements in SQL Server we usually encounter two words compilation and recompilation. Here I will through some light on…
- What if you forget the password of Database Master Key (DMK) by: Subhash
- SSIS (18)
- Search the contents of SSIS packages by: Subhash
February 24, 2010, 16:35
SSIS packages that are deployed to SQL Server or SSIS package Store are stored in msdb..sysdtspackages90 table. Not only SSIS packages but the Maintenance plans…
- How to start and stop windows service from SSIS by: Subhash
February 21, 2010, 12:06
To start or stop a windows service from SSIS package add a Script Task and in design script page use the below code. Replace the ComputerName and ServiceName ac…
- Tips to improve performance of Data Flow tasks by: Subhash
February 7, 2010, 12:45
SSIS does not have any performance evaluation or monitoring tool. So it is hard to correctly identify the culprit part of a poorly performing package. So proact…
- Avoid the asynchronous transformation as much as possible by: Subhash
February 7, 2010, 12:41
It is a bit confusing if you know asynchronous processing in Service Broker or an interface application because in these platforms asynchronous processing is im…
- Configure the Lookup cache modes by: Subhash
February 7, 2010, 12:38
Cache Mode specify the caching of data in lookup task. We can specify one of following three lookup mode for an Lookup task: 1. …
- Using CDC and MERGE by: Subhash
February 7, 2010, 12:37
Note: This method is applicable only for data transfer cases where data source is a table from SQL Server 2008 server. Data flow tasks are mostly used…
- Optimize OLEDB destination using Fast Load by: Subhash
February 7, 2010, 12:34
When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged …
- Replace the Sort Data Flow item with ORDER BY by: Subhash
February 7, 2010, 12:32
For some data operations like Merge and Merge Join transformations require sorted inputs. SSIS provide a task “Sort” to sort the input recordset. As we know…
- SSIS Checkpoint by: Subhash
February 7, 2010, 12:31
• Checkpoints enable a failed SSIS package to be restarted at the task where the execution was ended. …
- Comparison between T-SQL and SSIS expression by: Subhash
February 7, 2010, 12:29
An Expression is a combination of identifiers, literals, functions, and operators that returns a single data value. We are very familiar with functions and oper…
- Expression - The secret of Dynamic SSIS by: Subhash
February 7, 2010, 12:24
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single c…
- Redirect Error rows to log table by: Subhash
February 7, 2010, 12:23
The most of the error in Data Flow task occur while loading data in destination table. Because the data in source is not always compatible with destination stru…
- Transactions handling in SSIS package task by: Subhash
February 7, 2010, 12:18
In SSIS a transaction can be configured at any level of container (package, container or task). By default transaction is not implemented at any level even not …
- Container objects in SSIS by: Subhash
February 7, 2010, 12:16
Container is a manageable unit of work. Containers are used to define a scope of variables, to implement transaction on multiple tasks and to create a group of …
- Working with Multiple Constraints in Precedence Constraint Editor by: Subhash
February 7, 2010, 12:13
The below section in Precendence Constraint Editor is to manage Multiple constraints. We can understand the effect of Logical AND and Logical OR using following…
- Precedence Constraints in Control Flow by: Subhash
February 7, 2010, 12:03
To maintain a sequence of control flow tasks we specify a relationship between tasks using Precedence Constraint. Precedence constraint is the connection line a…
- Difference overview between Control Flow and Data Flow tasks by: Subhash
February 7, 2010, 12:00
Two most important sections in a SSIS package are Control flow tasks and Data flow tasks. But beginners commonly get confused while deciding where control flow …
- SSIS package elements by: Subhash
February 7, 2010, 11:57
SQL Server Integration Service is a platform to design the complete ETL process. It support almost all types of data sources, destinations and all possible tran…
- Search the contents of SSIS packages by: Subhash
Generated by Table of Contents Creator v1.6.1
by Mark Beljaars
