SQL server Database high performance queues

We have Queues everywhere. There are queues for asynchronously sending notifications like email and SMS in most websites. E-Commerce sites have queues for storing orders, processing and dispatching them. Factory Assembly line automation systems have queues for running tasks in parallel, in a certain order. Queue is a widely used data structure that sometimes have to be created in a database instead of using specialized queue technologies like MSMQ. Running a high performance and highly scalable queue using database technologies is a big challenge and it’s hard to maintain when the queue starts to get millions of rows queue and dequeued per day. Let me show you some common design mistakes made in designing Queue-like tables and how to get maximum performance and scalability from a queue implemented using simple database features.

Let’s first identify the challenges you have in such queue tables:

• The table is both read and write. Thus queuing and dequeuing impact each other and cause lock contention, transaction deadlocks, IO timeouts, etc. under heavy load.
• When multiple receivers try to read from the same queue, they randomly get duplicate items picked out of the queue, thus resulting in duplicate processing. You need to implement some kind of high performance row lock on the queue so that the same item never gets picked up by concurrent receivers.
• The Queue table needs to store rows in a certain order and read in a certain order, which is an index design challenge. It’s not always first in and first out. Sometimes Orders have higher priority and need to be processed regardless of when they are queued.
• The Queue table needs to store serialized objects in XML or binary form, which becomes a storage and index rebuild challenge. You can’t rebuild index on the Queue table because it contains text and/or binary fields. Thus the tables keep getting slower and slower every day and eventually queries start timing out until you take a downtime and rebuild the indexes.
• During dequeue, a batch of rows are selected, updated and then returned for processing. You have a “State” column that defines the state of the items. During dequeue, you select items of certain state. Now State only has a small set of values, e.g. PENDING, PROCESSING, PROCESSED, ARCHIVED. As a result, you cannot create index on “State” column because that does not give you enough selectivity. There can be thousands of rows having the same state. As a result, any dequeue operation results in a clustered index scan that’s both CPU and IO intensive and produces lock contention.
• During dequeue, you cannot just remove the rows from table because that causes fragmentation in the table. Moreover, you need to retry orders/jobs/notification N times in case they fail on the first attempt. This means rows are stored for longer period, indexes keep growing and dequeue gets slower day by day.
• You have to archive processed items from the Queue table to a different table or database, in order to keep the main Queue table small. That means moving large amount of rows of some particular status to another database. Such large data removal leaves the table highly defragmented causing poor queue/dequeue performance.
• You have a 24x7 business. You have no maintenance window where you can take a downtime and archive large number of rows. This means you have to continuously archive rows without affecting production queue-dequeue traffic.

Linked lists Vs List

string[] dataSet = new string[100];for (int i = 0; i < 99; i++){    dataSet[i] = string.Format("Sample Data {0}", i.ToString());}

This code allocates space for 100 string items and fills each one of them with a string. However, there are a few drawbacks in the regular array. What if there are more than 100 items used later on? The array needs to be extended, and if it was hardcoded to a hundred values, there could be some problems with overflowing data. Another problem is the inefficient memory usage when there are less than a hundred elements. In that case, a lot of memory is kept unused.

Linked lists tend to solve the two of the above problems. First of all, a linked list doesn’t have a single field for a value, but rather two – one with the stored data and one referencing the next value. These two fields together build a node, that is – a unit inside the linked list.

Difference between Read Uncommitted and Nolock - SQL server

The read-uncommitted isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.

The nolock table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.

The only difference between the two is that the read-uncommitted isolation level determines the locking mechanism for the entire connection and the nolock table hint determines the locking mechanism for the table that you give the hint to.

Sample:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.id

It is functionally equivalent to:

SELECT *
FROM Table1 T1 WITH(NOLOCK)
INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID

Query to search on specific string in database procedures

select name,OBJECT_DEFINITION(object_id) from sys.procedures
where OBJECT_DEFINITION(object_id) like '%With(Index%'

SQl Query to find out the unused Indexs in SQl server


SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
AND s.index_id is null
OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC

Adding XML config files along with package using Installshield 2016

> Installation Designer Tab> Left panel -> System Configuration

* Create a component for your existing development XML file and include the XML file in it. Set all of the attributes for the file properly (key file, target location, etc.)

*  Switch to XML view and use the import wizard. Only import the keys you want to change

* Once the wizard is done, select the file itself and set the XML File Destination to match the XML file you're modifying.

* Change the search patterns for the keys you want to modify. For some reason, the IDE shows this as the Element Name - this is actually the XPath query to find the element. 

* Delete anything in the attributes section that you aren't trying to change

*  Set the Value of your attribute as necessary  and set the operation to Append Value.

* Switch to advanced view and make sure that only "Update first matching element only" is checked (unless you want to replace multiple, identical elements )

Delegates Real Example.....

static void Main()
{
var mycar = new Car(20, 100, "baachi");
mycar.RegisterWithCarEngine(OnCarEngineEvent);
//NOW SPEED UP THE CAR
for (int i = 0; i < 6; i++)
{
mycar.AccelerateSpeed(20);
Console.ReadLine();
}
}
public class Car
{
#region Properties
public string PetName { get; set; }
public int MaximumSpeed { get; set; }
public int CurrentSpeed { get; set; }
#endregion
public Car()
{
MaximumSpeed = 100;
}
public Car(int currentSpeed, int maximumSpeed, string petName)
{
CurrentSpeed = currentSpeed;
MaximumSpeed = maximumSpeed;
PetName = petName;
}
//Define delegate
public delegate void CarEngineHandler(string msgForCaller);
private CarEngineHandler _listOfHandlers;
private bool carIsDead;
//registration function for the caller
public void RegisterWithCarEngine(CarEngineHandler methodToCall)
{
_listOfHandlers = methodToCall;
}
public void AccelerateSpeed(int speed)
{
//If this car is dead Send dead message.......
if (carIsDead)
{
if (_listOfHandlers != null)
{
_listOfHandlers("Sorry BOSS : Car Is Dead: OOOOPS");
}
}
else
{
CurrentSpeed += speed;
// If this car is almost dead,
if (MaximumSpeed - CurrentSpeed == 10 && _listOfHandlers != null)
{
_listOfHandlers("Carefull Buddy: Gonna Blow:::");
}
}
if (CurrentSpeed > MaximumSpeed)
{
carIsDead = true;
}
else
{
Console.WriteLine("Current Speed is :{0}", CurrentSpeed);
}
}
}

Call back functions

A callback method is one which is passed as an argument from another method which is invoked due to some kind of event. The 'call back' nature of the argument is that it returns it's result to the method that provided it as an argument - that is to say that it 'calls back' with the return value of the callback method.//An innocuous looking method which will become known as a callback method
//because of the way in which we will invoke it.
int meaningOfLife(void) {
return 42;
}
//An innocuous looking method which just takes an int and prints it to screen
void Print_A_Number(int a_Number) {
System.out.print(a_Number);
}
//invoking a method which passes another method as an argument in reaction to an event (the 'another' method - meaningOfLife - is therefore called a callback method) and the event - main() - is that the program is starting
void main() {
Print_A_Number(meaningOfLife);
}
Callbacks are so-called due to their usage with pointer languages. If you don't use one of those, don't labour over the name 'callback'. Just understand that it is just a name to describe a method that's supplied as an argument to another method, such that when that method is called (whatever condition, such as a button click, a timer tick etc) the callback method is therefore invoked, which returns a result to the calling method, which in turn returns a result to the event.

Call back functions

A callback method is one which is passed as an argument from another method which is invoked due to some kind of event. The 'call back' nature of the argument is that it returns it's result to the method that provided it as an argument - that is to say that it 'calls back' with the return value of the callback method.//An innocuous looking method which will become known as a callback method
//because of the way in which we will invoke it.
int meaningOfLife(void) {
return 42;
}
//An innocuous looking method which just takes an int and prints it to screen
void Print_A_Number(int a_Number) {
System.out.print(a_Number);
}
//invoking a method which passes another method as an argument in reaction to an event (the 'another' method - meaningOfLife - is therefore called a callback method) and the event - main() - is that the program is starting
void main() {
Print_A_Number(meaningOfLife);
}
Callbacks are so-called due to their usage with pointer languages. If you don't use one of those, don't labour over the name 'callback'. Just understand that it is just a name to describe a method that's supplied as an argument to another method, such that when that method is called (whatever condition, such as a button click, a timer tick etc) the callback method is therefore invoked, which returns a result to the calling method, which in turn returns a result to the event.

Best Example for StringBuilder

protected string GetRandomString(int numChars)
{
StringBuilder sb = new StringBuilder();
Random rand = new Random();
for (int i = 0; i < numChars; i++)
{
sb.Append(Convert.ToChar(rand.Next(32, 127)));
}
return (sb.ToString());
}

Attributes and Custom Attributes .Net

Why Attributes? and Custom attributes
Attributes are elements that allow you to add declarative information to your programs. This declarative information is used for various purposes during runtime and can be used at design time by application development tools. For example, there are attributes such as DllImportAttribute that allow a program to communicate with the Win32 libraries. Another attribute, ObsoleteAttribute, causes a compile-time warning to appear, letting the developer know that a method should no longer be used. When building Windows forms applications, there are several attributes that allow visual components to be drag-n-dropped onto a visual form builder and have their information appear in the properties grid. Attributes are also used extensively in securing .NET assemblies, forcing calling code to be evaluated against pre-defined security constraints. These are just a few descriptions of how attributes are used in C# programs.
The reason attributes are necessary is because many of the services they provide would be very difficult to accomplish with normal code. You see, attributes add what is called metadata to your programs. When your C# program is compiled, it creates a file called an assembly, which is normally an executable or DLL library. Assemblies are self-describing because they have metadata written to them when they are compiled. Via a process known as reflection, a program's attributes can be retrieved from its assembly metadata. Attributes are classes that can be written in C# and used to decorate your code with declarative information. This is a very powerful concept because it means that you can extend your language by creating customized declarative syntax with attributes.
This tutorial will show how to use pre-existing attributes in C# programs. Understanding the concepts and how to use a few attributes, will help in finding the multitude of other pre-existing attributes in the .NET class libraries and use them also.

get the permissions of your sql sevrer

SELECT * FROM fn_my_permissions(NULL, 'SERVER');USE AdventureWorks;SELECT * FROM fn_my_permissions (NULL, 'DATABASE');GO

Introduction to Triggers

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.
The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.


SET NOCOUNT ON
CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')
-- Results --Apr 28 2001 9:56AM

When to Use Triggers
There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.

SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSPGO
/* SELECT from TempTable */
SELECT *FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue
INTINSERT INTO @retTestFnSELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO

Date Format using CONVERT function in SQL Server

Today, i would like to list out various styles of date display formats. These are very helpful for the Front-end application programmers where they want to display different date formats.

Various date style formats can be produced with CONVERT function.
CONVERT function need 3 parameters.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The first parameter is return data type, second parameter is expression, third parameter which is optional parameter defines the style.

NOTE: Do not apply CONVERT function on date column when it is Indexed.
with out waiting lets look at the various styles of date values.
The below query produces the output in
MON DD YYYY HH:MIAMPM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),100)
--Output (MON DD YYYY HH:MIAMPM)
--Jul 7 2009 2:19PM
The below query produces the output in MM/DD/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),101)
--Output (MM/DD/YYYY)
--07/07/2009
The below query produces the output in YYYY.MM.DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),102)
--Output (YYYY.MM.DD)
--2009.07.07
The below query produces the output in DD/MM/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),103)
--Output (DD/MM/YYYY)
--06/07/2009
The below query produces the output in DD.MM.YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),104)
--Output (DD.MM.YYYY)
--06.07.2009
The below query produces the output in DD-MM-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),105)
--Output (DD-MM-YYYY)
--06-07-2009
The below query produces the output in DD MON YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),106)
--Output (DD MON YYYY)
--06 Jul 2009
The below query produces the output in MON DD,YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),107)
--Output (MON DD,YYYY)
--Jul 06, 2009
The below query produces the output in HH24:MI:SS format.
SELECT CONVERT(VARCHAR(30),GETDATE(),108)
--Output (HH24:MI:SS)
--14:24:20
The below query produces the output in MON DD YYYY HH:MI:SS:NNN AMPM format.
Use 113 style to get date and time with nano seconds in AM/PM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
--Output (MON DD YYYY HH:MI:SS:NNN AMPM)
--Jul 7 2009 2:24:35:490PM
The below query produces the output in MM-DD-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),110)
--Output (MM-DD-YYYY)
-- 07-07-2009
The below query produces the output in YYYY/MM/DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),111)
--Output (YYYY/MM/DD)
--2009/07/07
The below query produces the output in YYYYMMDD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),112)
--Output (YYYYMMDD)
--20090707
The below query produces the output in MON DD YYYY HH24:MI:SS:NNN format.
Use 113 style to get date and time with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
--Output (MON DD YYYY HH24:MI:SS:NNN)
--07 Jul 2009 14:26:24:617
The below query produces the output in HH24:MI:SS:NNN format.
Use 114 Style to extract Time part with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),114)
--Output (HH24:MI:SS:NNN)
--14:26:48:953

Information Schema Views in SQL Server

In this article, I would like to list out the SQL Server metadata tables which we need in our day-to-day work.
SELECT * FROM Information_Schema.CHECK_CONSTRAINTS
-- Contains one row for each CHECK constraint in the current database.
SELECT * FROM Information_Schema.COLUMN_DOMAIN_USAGE
-- Contains one row for each column, in the current database, that has a user-defined data type.
SELECT * FROM Information_Schema.COLUMN_PRIVILEGES
--Contains one row for each column with a privilege either granted to or by the current user in the current database
SELECT * FROM Information_Schema.COLUMNS
--Contains one row for each column accessible to the current user in the current database.
SELECT * FROM Information_Schema.CONSTRAINT_COLUMN_USAGE
--Contains one row for each column, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.CONSTRAINT_TABLE_USAGE
--Contains one row for each table, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.DOMAIN_CONSTRAINTS
--Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it.
SELECT * FROM Information_Schema.DOMAINS
--Contains one row for each user-defined data type accessible to the current user in the current database.
SELECT * FROM Information_Schema.KEY_COLUMN_USAGE
--Contains one row for each column, in the current database, that is constrained as a key.
SELECT * FROM Information_Schema.PARAMETERS
--Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
SELECT * FROM Information_Schema.REFERENTIAL_CONSTRAINTS
--Contains one row for each foreign constraint in the current database.
SELECT * FROM Information_Schema.ROUTINE_COLUMNS
--Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
SELECT * FROM Information_Schema.ROUTINES
--Contains one row for each stored procedure and function accessible to the current user in the current database.
SELECT * FROM Information_Schema.SCHEMATA
--Contains one row for each database that has permissions for the current user.
SELECT * FROM Information_Schema.TABLE_CONSTRAINTS
--Contains one row for each table constraint in the current database.
SELECT * FROM Information_Schema.TABLE_PRIVILEGES
--Contains one row for each table privilege granted to or by the current user in the current database.
SELECT * FROM Information_Schema.TABLES
--Contains one row for each table in the current database for which the current user has permissions.
SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE
--Contains one row for each column, in the current database, used in a view definition.
SELECT * FROM Information_Schema.VIEW_TABLE_USAGE
--Contains one row for each table, in the current database, used in a view.
SELECT * FROM Information_Schema.VIEWS
--Contains one row for views accessible to the current user in the current database.

Interview Questions

.Net Interview Questions
ASP.NETASP.Net Interview Questions - Part 1
ASP.Net Interview Questions - Part 2
ASP.Net Interview Questions - Part 3
ASP.Net Interview Questions - Part 4
ASP.Net Interview Questions - Part 5
ASP.Net Interview Questions - Part 6
ASP.Net Interview Questions - Part 7
ASP.Net Interview Questions - Part 8
ASP.Net Interview Questions - Part 9
ASP.Net Interview Questions - Part 10
ASP.Net Interview Questions - Part 11
ASP.Net Interview Questions - Part 12
C# C# Interview Questions

Delete Duplicate records in sql server

WITH CTE (SecondCol,ThirdCol, DuplicateCount)
AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

how to remove the LOGFILE and perform some operations from cmd prompt

SELECT name,recovery_model_desc FROM sys.databases
and check whether the database is 'FULL' mode or 'SIMPLE'..if it s FULL we have to remove the LOGFILE of database.
go to database ...> detach.. next goto logfile and rename it..of that database
again we have to attach the only .mdf file (instead of .mdf and .ldf files to database)
=====================
sqlcmd -?
we can change the sqlserver sa password from cmd prompt very easily
EXEC SP_PASSWORD NULL,'venki','sa'
GO

Bind the Help To Project and Binding datasource to report viewver

Dim hlppath As String = Application.StartupPath & "\Ayuda\Espiga.chm"
System.Windows.Forms.Help.ShowHelp(Me, hlppath)


Private Sub InitReports(ByVal ReportName As String, ByVal DS As DataSet, ByVal ParameterName() As String, ByVal ParameterValue() As String)
Try
Dim ParamList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
Dim reportDatasource As New Microsoft.Reporting.WinForms.ReportDataSource
ReportViewer1.Clear()
ReportViewer1.Reset()
'Add Tables to LocalReport's DataSource
For Each table As DataTable In DS.Tables
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource(DS.DataSetName & "_" & table.TableName, table))
Next
'Load parameters
For i As Integer = 0 To ParameterName.Length - 1
ParamList.Add(New Microsoft.Reporting.WinForms.ReportParameter(ParameterName(i).Trim, ParameterValue(i).Trim))
Next i
'Embed the LocalReport to the ReportViewer
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = Me.GetType().Namespace & "." & ReportName
'Set the parameters to the LocalReport
Me.ReportViewer1.LocalReport.SetParameters(ParamList)
Me.ReportViewer1.RefreshReport()
Catch ex As Exception
End Try
End Sub