SQL Server and MS BI With a Geek Touch

Basic SQL Server Questions

What is RDBMS (Relational database management system)?

RDBMS is a database management system (DBMS) that is based on the relational model. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values

What is the Difference between DBMS and RDBMS?

A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored. Read more…

Recompiling an object is advantageous when ‘indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. sp_recompile is a system stored procedure in SQL that will recompile an object the next time it runs.  By recompiling , you can reoptimize the queries.

Below is the query to recompile all the SPs in the database.

Declare @name nvarchar(125),@cmd nvarchar(300)

Declare SPList cursor FOR      /* Declare Cursor to loop through all the SPs in the DB */
Select Name from sys.objects
where type = ‘P’

Open SPList    

fetch next from SPList     /* fetch Record from cursor*/
into @Name 

While @@FETCH_STATUS = 0
begin
Select @cmd = ‘EXEC sp_recompile  ['+@Name+']‘

Exec @cmd  /* recompile the SP */

fetch next from SPList    /* fetch next SP name from Cursor */
into @Name

End

Close SPList
Deallocate SPList