SQL Server DBA questions

1. Difference between Cluster and Non Cluster Index
2. What is Page
3. Difference between Procedure, View, Function.
4. What's the difference between where and having.
5. What is fact and dimension
6. What's the difference between delete and truncate.
7. What is index tell me brief.
8. Types of temp table.
9. How you will get the data.
10. What was the most difficult scenario faced in ssis.
11. What kind of errors you can handled in ssis.
12. Break points.
13. Check points.
14. Event handler.
15. Performance tuning in SSIS
16. Deployment in ssis.
17. Blocking transformations.
18. SCD transformation brief explanation.
19. Term extraction.
20. Isolation properties.
21. Derived column expression.
22. Self join.
23. Types of joins explain briefly.
24. Character map transformation purpose.
25. Real time scenarios in sql-server.
26. Performance tuning in SQL Server.
27. Merge join.
28. Union all.
29. Multicast.
30. Error handling.

SQL Server Instance in Run Time

SELECT @@CONNECTIONS 'CONNECTIONS'

SELECT @@CPU_BUSY 'CPU_BUSY'

SELECT @@CURSOR_ROWS 'CURSOR_ROWS'

SELECT @@DATEFIRST 'DATEFIRST'

SELECT @@DBTS 'DBTS'

SELECT @@DEF_SORTORDER_ID 'DEF_SORTORDER_ID'

SELECT @@DEFAULT_LANGID 'DEFAULT_LANGID'

SELECT @@ERROR 'ERROR'

SELECT @@FETCH_STATUS 'FETCH_STATUS'

SELECT @@IDENTITY 'IDENTITY'

SELECT @@IDLE 'IDLE'

SELECT @@IO_BUSY 'IO_BUSY'

SELECT @@LANGID 'LANGID'

SELECT @@LANGUAGE 'LANGUAGE'

SELECT @@LOCK_TIMEOUT 'LOCK_TIMEOUT'

SELECT @@MAX_CONNECTIONS 'MAX_CONNECTIONS'

SELECT @@MAX_PRECISION 'MAX_PRECISION'

SELECT @@MICROSOFTVERSION 'MICROSOFTVERSION'

SELECT @@NESTLEVEL 'NESTLEVEL'

SELECT @@OPTIONS 'OPTIONS'

SELECT @@PACK_RECEIVED 'PACK_RECEIVED'

SELECT @@PACK_SENT 'PACK_SENT'

SELECT @@PACKET_ERRORS 'PACKET_ERRORS'

SELECT @@PROCID 'PROCID'

SELECT @@REMSERVER 'REMSERVER'

SELECT @@ROWCOUNT 'ROWCOUNT'

SELECT @@SERVERNAME 'SERVERNAME'

SELECT @@SERVICENAME 'SERVICENAME'

SELECT @@SPID 'SPID'

SELECT @@TEXTSIZE 'TEXTSIZE'

SELECT @@TIMETICKS 'TIMETICKS'

SELECT @@TOTAL_ERRORS 'TOTAL_ERRORS'

SELECT @@TOTAL_READ 'TOTAL_READ'

SELECT @@TOTAL_WRITE 'TOTAL_WRITE'

SELECT @@TRANCOUNT 'TRANCOUNT'

SELECT @@VERSION 'VERSION'

Microsoft Excel Shortcut Keys

Ctrl + A Selects the entire worksheet. If the worksheet contains data, and selects the current region

Ctrl + B Applies or removes bold formatting

Ctrl + C Copies the selected cells

Ctrl + D Copies the value from the cell above the active cell into the cell or the Formula Bar.

Ctrl + E Flash Fill - Automatically fills in values

Ctrl + F Displays the Find and Replace dialog box, with the Find tab selected.

Ctrl + G Displays the Go to dialog box

Ctrl + H Displays the Find and Replace dialog box, with the Replace tab selected.

Ctrl + I Applies or removes italic formatting

Ctrl + K Inserts a hyperlink

Ctrl + L Displays the Create Table dialog box

Ctrl + N Creates a new, blank workbook

Ctrl + O Displays the Open dialog box to open a file

Ctrl + P Displays the Print dialog box

Ctrl + S Save a workbook/sheet

Ctrl + T Displays the Create Table dialog box

Ctrl + U Applies or removes underlining

Ctrl + V Paste

Ctrl + W Closes the selected workbook window

Ctrl + X Cuts the selected cells

Ctrl + Y Redoes the last command or action

Ctrl + Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

DateTime Validation in VB.Net DataGridView Column

Hi, Here I write the code for DateTime input validation of a DataGrid cell in VB.Net. 

Here is the Form Design. Now I code for the input validation of Date Column and Time Column.

Now my code is written under CellValidating Event.

Private Sub DataGridView1_CellValidating(sender As Object, e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating

        Select Case e.ColumnIndex

            Case 1,2

                Me.DataGridView1.Rows(e.RowIndex).ErrorText = ""

                Dim NewDate As Date

                If DataGridView1.Rows(e.RowIndex).IsNewRow Then Return

                If Not Date.TryParse(e.FormattedValue.ToString(), NewDate) Then

                    e.Cancel = True

                    MsgBox("Enter correct DateTime format", MsgBoxStyle.Critical, "ERROR!")

                    DataGridView1.CurrentCell.Value = ""

                End If

        End Select

    End Sub

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

HASHBYTES in SQL Server

In SQL Server, for simple hash code encryption like Password Encryption, we can use the HASHBYTES function to encrypt the string.

This is a built-in cryptographic function with hashing algorithms like MD2, MD4, MD5, SHA1, SHA2 (256 and 512).

In these algorithm, SHA2 (256 and 512) are introduced in SQL Server 2008.

The other MD and SHA1 algorithms are available in SQL Server 2005 onwards.

The output conforms to the algorithm standard:

128 bits (16 bytes) for MD2, MD4, and MD5

160 bits (20 bytes) for SHA and SHA1

256 bits (32 bytes) for SHA2_256

512 bits (64 bytes) for SHA2_512

I strongly recommended only SHA2 (256 and 512). All the earlier versions of MD and SHA1 are depreciated.

Try this SQL Queries:

DECLARE @input NVARCHAR(50); 

SET @input = 'arkaguptablog'

SELECT HASHBYTES('MD2', @input) as 'MD2 Output', LEN(HASHBYTES('MD2', @input)) AS Length;

SELECT HASHBYTES('MD4', @input) as 'MD4 Output', LEN(HASHBYTES('MD4', @input)) AS Length;

SELECT HASHBYTES('MD5', @input) as 'MD5 Output', LEN(HASHBYTES('MD5', @input)) AS Length;

SELECT HASHBYTES('SHA', @input) as 'SHA Output', LEN(HASHBYTES('SHA', @input)) AS Length;

SELECT HASHBYTES('SHA1', @input) as 'SHA1 Output', LEN(HASHBYTES('SHA1', @input)) AS Length; 

SELECT HASHBYTES('SHA2_256', @input) as 'SHA-256 Output', LEN(HASHBYTES('SHA2_256', @input)) AS Length;

SELECT HASHBYTES('SHA2_512', @input) as 'SHA-512 Output', LEN(HASHBYTES('SHA2_512', @input)) AS Length;

Output Result :

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

Datetime Picker in VB.Net Textbox

Hi, Here I discuss about DateTime in VB.Net 
We all use DateTimePicker in VB Form for Date field. 
But I use DateTextBox in VB Form for Date field with watermark and Validation. 
Here not use DateTimePicker, simply create a class and use this. 
Try this code:

Imports System

Imports System.Drawing

Imports System.Windows.Forms

Public Class DateTextBox

    Inherits TextBox

    Public Sub New()

        MyBase.New()

        SetStyle(ControlStyles.AllPaintingInWmPaint Or ControlStyles.OptimizedDoubleBuffer, True)

        UpdateStyles()

        AddHandler TextChanged, AddressOf TextChange

        AddHandler FontChanged, AddressOf FontChange

        AddHandler KeyPress, AddressOf KeyPressed

        AddHandler Validated, AddressOf Validate

        CreateContainer()

    End Sub

    Dim WatermarkString0 As String = "DD/MM/YYYY"

    Dim WatermarkFont0 As Font = New Font(Me.Font, FontStyle.Regular)

    Dim WatermarkColor0 As Color = Color.Gray

    Dim WatermarkMaxLength As Integer

    Dim WatermarkContainer As Panel

    Private Sub CreateContainer()

        WatermarkContainer = New Panel

        WatermarkContainer.Width = Me.Width - 4

        WatermarkContainer.Height = Me.Height

        WatermarkContainer.Location = New Point(2, 0)

        WatermarkContainer.Anchor = AnchorStyles.Left + AnchorStyles.Right

        AddHandler WatermarkContainer.Paint, AddressOf WatermarkPaint

        AddHandler WatermarkContainer.Click, AddressOf WatermarkClick

        Me.Controls.Add(WatermarkContainer)

    End Sub

    Private Sub RemoveContainer()

        Me.Controls.Remove(WatermarkContainer)

    End Sub

    Private Sub WatermarkClick(sender As Object, e As EventArgs)

        Me.Focus()

    End Sub

    Private Sub WatermarkPaint(sender As Object, e As PaintEventArgs)

        Dim g As Graphics = e.Graphics

        g.DrawString(WatermarkString0, WatermarkFont0, New SolidBrush(WatermarkColor0), New PointF(-2.0!, 1.0!))

    End Sub

    Private Sub TextChange()

        If Text.Length = 0 Then

            CreateContainer()

        Else

            RemoveContainer()

        End If

    End Sub

    Private Sub FontChange()

        WatermarkFont0 = New Font(Me.Font, FontStyle.Regular)

    End Sub

    Private Sub KeyPressed(sender As Object, e As System.Windows.Forms.KeyPressEventArgs)

        If (e.KeyChar < Chr(47) Or e.KeyChar > Chr(57)) And (e.KeyChar <> Chr(8)) And (e.KeyChar <> Chr(127)) Then

            e.Handled = True

            MsgBox("Enter correct Date format DD/MM/YYYY", MsgBoxStyle.Critical, "ERROR!")

        End If

    End Sub

    Private Sub Validate(sender As Object, e As System.EventArgs)

        Dim d As Date

        If Text <> "" Then

            Try

                d = Text

                Text = d

            Catch ex As Exception

                MsgBox("Enter correct Date format DD/MM/YYYY", MsgBoxStyle.Critical, "ERROR!")

                Me.Select()

            End Try

        End If

    End Sub

    Protected Overrides Sub OnInvalidated(e As System.Windows.Forms.InvalidateEventArgs)

        MyBase.OnInvalidated(e)

        WatermarkContainer.Invalidate()

    End Sub

    Public Property WatermarkText As String

        Get

            Return WatermarkString0

        End Get

        Set(value As String)

            WatermarkString0 = value

            Me.Invalidate()

        End Set

    End Property

    Public Property WatermarkColor As Color

        Get

            Return WatermarkColor0

        End Get

        Set(value As Color)

            WatermarkColor0 = value

            Me.Invalidate()

        End Set

    End Property

End Class

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

https://plus.google.com/+ArkaGupta-arkagupta/posts