This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter. I know above statement is not digestive enough so lets understand this by an example. Suppose , I have a table tblStudent with …
Hello friends, Just wanted to share some technical words here again it is good to know 1) OLAP : On Line Analytical Processing 2) MOLAP :- Multidimensional On Line Analytical Processing 3) ROLAP :- Relational On Line Analytical Processing 4) HOLAP :- Hybrid On Line Analytical Processing 5) RTOLAP :- Real Time On Line …
Friends, In last post post #54 we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so. Now our task is to run maintenance of this highly fragmented table. So you have to run following command “ALTER TABLE TABLENAME REBUILD; For example I run the Rebuild …
Heap table:- A table without cluster index is called Heap table. Now you are thinking why we are talking about this. So as SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table. Now our next question is how to determine …
Dear Friends, Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again …
As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB by the name it …
Problem:- Sometimes it happened that you need to run a single statement on entire tables which exists in database. so most of the time we think of cursor which run for each sys.objects (table) and we execute the dynamic statement by replacing table name. Solution: Although the solution we are thinking is correct there is …
Problem:– How to count large number of rows for specific condition ? Solution Suppose you have a large table which having more than millions of records and you want to count no of rows for specific condition which itself contain more than 100 millions records. Now in this case when we use COUNT function …
Sometimes , It may required that you want a query who can provide you result which have phonetic similarity. Lets understand this by example Suppose you want to search a record whose pronunciation or phonetic sound are similar or close to then in that case you can use SOUNDEX property of SQL SERVER which is …
Problem:- Sometimes it may require that whatever you have updated you want to know what was the previous value of that column? It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ? Solution:- Now to achieve this the basic step …