A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database. Stored Procedure have a securable unit of code. When the stored proc is executed for… Continue reading
VARCHAR[(n)] Null-terminated Unicode character string of length n, with a maximum of 255 characters. If n is not supplied, then 1 is assumed. OR 8000 OR The business logic is the aspx.cs or the aspx.vb where the code is being written. The presentation logic is done with .aspx extention.
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The stored procedures return a value of 0 when no errors were encountered…. Continue reading
You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters. USE master… Continue reading
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order: The stored procedure in the master database. The stored procedure based on any qualifiers provided (database name or owner)…. Continue reading
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and… Continue reading
SQL Server uses these resource lock modes. Lock mode Description Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement. Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are… Continue reading
If we give proper indexes on a table so that any queries written against this table can run efficiently. As your data sets grow over time, SQL Server will continue to rebuild indexes and move data around as efficiently as possible. This property is known as Indexing.
Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQL Server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys. Referential integrity ensures that key values are consistent across tables. Such consistency requires that… Continue reading
Basically in SQL server DBA teams apply the password policies (i.e. enforce password policy & enforce password expiration) in sql login while creating new login for improve the security. But once password policy applied there are no options available in SSMS for getting the alert notification to DBA team about… Continue reading