Posts

Showing posts with the label sql

Difference between array, arraylist, List, Hashtable, Dictionary and SortedList in c#

-
Basic difference is that arrays are of fixed size. Whereas an ArrayList implements the list data structure and can dynamically grow. While arrays would be more performance that a list, a list would be far more flexible since you don't need to know the required size initially. Array - represents an old-school memory array - kind of like a alias for a normal type[] array. Can enumerate. Can't grow automatically. I would assume very fast insertion, retrieve and speed. ArrayList - automatically growing array. Adds more overhead. Can enum., probably slower than a normal array but still pretty fast. These are used a lot in .NET List - one of my favorites - can be used with generics, so you can have a strongly typed array, e.g. List . Other than that, acts very much like ArrayList. Hashtable - plain old hashtable. O(1) to O(n) worst case. Can enumerate the value and keys properties, and do key/val pairs. Dictionary - same as above only strongly typed via generics, such a...
Post a Comment
Read more

What is difference between DELETE and TRUNCATE commands?

-
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.   1. TRUNCATE: TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.  TRUNCATE cannot be rolled back.  TRUNCATE is DDL Command. TRUNCATE Resets identity of the table   2. DELETE:  DELETE removes row...
Post a Comment
Read more

What is SQL Server Agent in sql?

-
SQL Server agent plays an important role in the day- to-day tasks of a database administrator (DBA).  It is often overlooked as one of the main tools for SQL Server management.  Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.
Post a Comment
Read more

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

-
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.  Many- to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table
Post a Comment
Read more

What is NOT NULL Constraint in sql?

-
A NOT NULL constraint enforces that the column will not accept null values. The NOT NULL constraints are used to enforce domain integrity, as the check constraints.
Post a Comment
Read more

Advantages of using Stored Procedures?

-
1. Stored procedure can reduced network traffic and latency, boosting application performance. 2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. 3. Stored procedures help promote code reuse. 4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. 5. Stored procedures provide better security to your data.
Post a Comment
Read more

What is PRIMARY KEY in sql?

-
A PRIMARY KEY constraint is a unique identifier for a row within a database table.  Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.  The primary key constraints are used to enforce entity integrity.
Post a Comment
Read more

What is Log Shipping in sql?

-
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.  
Post a Comment
Read more

sql interview questions and answers part 2

-
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
Post a Comment
Read more

sql interview questions and answer part 1

-
What's the difference between a primary key and a unique key? Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow Nulls, but unique key allows one NULL only.
Post a Comment
Read more

differences between function and stored procedure?

-
1) Function returns only one value but procedure returns one or more than one value. 2) Function can be utilized in select statements but that is not possible in procedure. 3) Procedure can have an input and output parameters but function has only input parameters only. 4) Exceptions can be handled by try catch block in procedures but that is not possible in function.
Post a Comment
Read more