Advance SQL Server

How easy or difficult to write dynamic SQL ? TIP#110

This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation. This seems very easy but we need to cast the  parameters in VARCHAR and sometimes we stuck in single code. I am sure this happened with all of us.  Let’s understand first …

An interesting setting for NULL but don’t use it for future TIP #109

As we discussed earlier in TIP#103 for NULL in which I shared that we have to take extra care for NULL. Now in this tip I would like to share one of the interesting setting for NULL. Although it is just for knowledge but don’t use it because it is deprecated in future version and …

A big issue when try to Alter user define table type structure TIP #108

  I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57. Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type …

A myth about view TIP #102

  I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ? I  hope everyone who is reading this article will be aware of what is …

Bulk Copy Program (BCP)–A simple way to export data in a file TIP#101

  Let’s consider a scenario where the end user require a CSV file of all the records in a table then BCP is one of the simplest way. BCP stands for Bulk copy program. By the name you got the feeling of lots of data . Although, there are various options & parameters available with …

How to Configure E-mail in SQL Server Step by Step–TIP #96

  For different reason we need to send database report , data and other SQL Server database related stuff using E-mail . SQL  Server provides E-mail functionality using “Database Email” feature. We can setup any E-mail  in few simple steps. Let’s follow below steps Step 1:- Open “Management” option of object Explorer in SQL SERVER  …

How easy to determine table dependencies ? TIP # 86

  Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides. By using this stored procedure we can easily determine all the dependencies of particular table. The stored procedure is sp_msdependencies We can use this stored procedure as shown below Execute sp_msdependencies …

How to configure memory in SQL SERVER ? TIP#83

  One of the good sentence I remember “When someone has teeth he/she is not having nuts and when someone has nuts he/she not having teeth”. Just joke a part. You understand what I mean to say here. If you have the resources then utilize it. One of the most most important aspect in performance …

How to Enable / Disable Xp_cmdShell in SQL SERVER? TIP #82

Friends, Sometimes it might be possible that you have to run dos command from SQL SERVER. In such situation you have to enable the xp_cmdShell option of  sql configuration. To enable this we can write following statement EXEC sp_configure ‘show advanced options’, 1;GORECONFIGURE;GOEXEC sp_configure ‘xp_cmdshell’, 1;GORECONFIGURE;GO Just wanted to share that it can be a …