These four principles are referred to as ‘ACID’, and each letter is an acronym for one property of RDBMS systems that is non-negotiable for the sake of the integrity of the system. Atomic(ity) – The principle that each transaction is ‘all-or-nothing’, i.e. it either succeeds or it fails, regardless of external factors such as power... » read more
Some best practices for moving data from one table to another table. Use TRUNCATE instead of DELETE If you need to clear the data in a table, use TRUNCATE instead of DELETE. TRUNCATE lock the table instead of at each row. No triggers are activated and no logs are generated resulting in faster performance. Note:... » read more
One useful tool that SQL Server offers is the ability to see query execution plans. An execution plan shows you how SQL Server breaks down a query and also identified where issues might exist within the execution plan. Once you have identified statements that take a long time to complete, you can then look at... » read more
SQL Server does not have a “results cache” and the second execution is not “practically free.”SQL Server does have a “buffer cache” and the second execution is “faster, but not free.” The SQL Server buffer cache holds data pages in memory, in the exact form that they reside on disk. The second execution will not have to perform... » read more
SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data. Article/Publications An article is the basic unit of SQL Server Replication. An article can consist of tables, stored procedures, and views. A Publication... » read more
When using the full recovery model backup option, transactional logs will be generated every time there is a transaction being made. The transaction log will keep accumulate until a full, differential, transaction log backup has been made. Only then will the logs be cleared up. In databases where there are high transaction being made, the... » read more
Indexes are used to speed-up query processes in a database. They are similar to an index for a book. If you need to go to a particular chapter you can go to the index, find the page number of the chapter and go directly to the page. Database index works the same way. If you... » read more
1. Use SET NOCOUNT ON SQL Server return information messages when running statements thus increasing network traffic. These messages can be suppressed by setting the NOCOUNT ON to decrease network traffic. 2. Use fully qualified procedure name A fully qualified object name is server.database.schema.objectname. SQL Server can swiftly find the complied plan instead of looking... » read more
SSRS, SSIS, and SSAS are all products/services offered by Microsoft. Different services comes in different editions. SQL Server Reporting Services (SSRS) Provide a framework of reporting mechanisms such as the Report Builder, Report Designer, Report Manager, and Report Server that work together through a web interface to enable the development of interactive reporting solutions. Report... » read more
There are two database backup options you can set for the database. Full Recovery Model Simple Recovery Model Full recovery model allows you to restore your database to the last time a transaction was made to the database by using transaction logs. Simple recovery model only allows you to restore your database since the last... » read more