Search results for: SQL Tips

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database


SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:


SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.


SQL SERVER: Community Tech Days in our City at Ahmedabad on 3rd October 2009

Microsoft Community Tech Days are in 11 cities in INDIA with 19 insightful Technical Sessions.


SQL SERVER: Difference between DELETE and TRUNCATE commands

We interviewed many people my company as recruitments are going on for developers. When I asked this SQL SERVER question to person "What is difference between DELETE and TRUNCATE in SQL SERVER?". 
I got the following  answers from most of them are, which are incorrect:
1. I can not use WHERE condition with TRUNCATE command
2. I can not use TRUNCATE command if foreign key is there on table.
3. TRUNCATE is faster than the DELETE, as  DELETE write records them in Log file in case it is needed to rollback in future from LOG files. etc..
These answers are correct. I also got this answer, which is Incorrect: 


SQL SERVER: Check if Node exists in XML or not

Today, I have one requirement to check dynamically if a node exists in my xml or NOT.



Recently I have following situation:
I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so.

SQL SERVER: Read values from Comma Separated variable


SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.


SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.
Let me share this thing with all of you.


SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.
For example, How much space occupied by each table.