March 3, 2015

Get Missing Index details from Activity Monitor Screen in SQL to get more performance

Create Missing index using SQL Activity Monitor to process improvement

If you want to improve performance of application then keep view on activity monitor in SQL and go to long executed query line then you can observe from recent expensive query window screen . You can see average duration of query execution.

To get Missing Index details from Activity Monitor Screen in SQL to get more performance you can do following steps.

Show Query execution plan

Missing Index
/*

Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 93.6536%.
*/

/*
USE [DB_TEST]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[GENERALJOURNALACCOUNTENTRY] ([CREATEDTRANSACTIONID])

GO
*/

Once you get index you can create index by directly executing query or you can create index manually. You can create index in query execution process time is very high.

February 26, 2015

Solution for SQL Database Backup Restore Error

Sql Error Description

Facing Below Error during Restoring of SQL Database to existing database or newly created database.

Restore of database 'MicrosoftDynamicsAx' failed.
(  Microsoft.SqlServer.Management.RelationalEngineTasks)


System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)

Sql database Restore error

Sql database Restore error

Solution:

This Error due to you have take Full SQL Backup in Read only mode. To solve this issue you can try below steps.

1.Untick tail log backup checkbox .If you don't know about Tail log backup option then search on google to know more about tail log backup.

2. Second option is you can take full backup of database again then restore then file on database then you can deleted backed up file to free the disk space.

Intranet and Extranet,Artificial Neural Networks,Server and Workstation

Intranet and Extra net

An Intranet is a private network

(LAN) that uses the TCP/IP protocol suite. However, access to the network is limited only to the users inside the organization.

The network uses application

programs defined for the global

Internet such as HTTP, and may

have web servers, print servers, file servers etc.

Extra net

Private network that uses the Internet protocol and the public telecommunication system

Shares part of a business's information with suppliers, vendors, partners, customers or other businesses.
Viewed as part of a company's intranet that is extended to users outside the company.


Artificial Neural Networks

ARTIFICIAL NEURAL NETWORKS

•Neural Networks can supplement the enormous potential of traditional computers with the ability to make sensible decisions and to learn by experience.

•Neural Computation is done by a dense mesh of computing nodes and connections. They operate collectively and simultaneously on data inputs.

•Antennas are being used to solve many real world problems. They have an ability to derive meaning from complicated or imprecise data and can be used to extract patterns and detect trends that are too complex to be noticed by either humans or other computer techniques


•Applications of neural networks, that give an impression of its achievements, include
(i) text to speech transformation
(ii) Picture data compression
(iii) recognition of handwriting
(iv) explosives detector
(v) signature examiner and many more.

Server and Workstation


Individual workstations (clients) have access to the resources available on the file servers.

Novell Netware and Windows NT Server are

examples of client/server network operating

systems.

Advantages of Server/Workstation

Resources and data security are controlled through the server.

Any or all elements can be replaced individually as needs increase.

New technology can be easily integrated into the system.

Server can be accessed remotely and across multiple platforms.


Disadvantages of Server/Workstation

Requires initial investment in dedicated server.

Large networks will require staff to ensure efficient operation.

When server goes down, operations will cease across the network.

Structured query language,SQL Query Processing

Structured query language

The common commands and methods used in it are grouped into the following categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Logical Connectors, Comparison operators, Aggregate functions, Joins, Sub query, Relational operators, Where clause etc.

Data definition language covers SQL commands that create and destroy database tables.

Data manipulation language covers SQL commands that create, change or destroy the information in a database table or the structure of the table.

Data control language covers protection to the database such as granting access to the tables.

Comparison operators cover those operators that allow for comparison between two items.

Aggregate function deals with those standard SQL functions that allow programmers to narrow the search of a database.

Joins category covers those items that allow information from two or more tables to be related.


SQL Query Processing

SQL QUERY PROCESSING

Limitations of computer,Introduction of Internet

Limitations of computer

1. No Feelings: Computers are not living. Hence, can’t make judgment of its own. Its instructions are based on information given to it in form of program.
2. It is Dumb: Computer posses no intelligence of it’s own. It’s I.Q. is zero. Computer can’t take it’s own decision in this regard.


Definition of  Internet

Internet is a worldwide network that provides an infrastructure to connect universities, government offices, companies, students, scientists, researchers and private individuals.
A machine to be on the Internet means it runs TCP/IP protocol stack, has an IP address, and has the ability to send IP packets to all the other machines on the Internet. A private individual having a personal computer can call up an Internet service provider using a modem, be assigned a temporary IP address, and send IP packets to other Internet hosts. 


An Internet consists of a set of connected networks that act as an integrated whole. The Internet provides universal interconnections while allowing individual groups to use whichever network hardware is best suited to their needs. As a network of networks, it provides a capability for communication to take place between research institutions, individuals, and among all ‘Internet Citizens’.

As a complex system of interlinked networks, the Internet supports millions of ‘servers’ computers housing large volumes of all sorts of information. The Internet is where millions of friends and strangers can chat. It lets people browse through thousands of on –line libraries, play new games, and trade software. Another feature of the Internet is that it has no geographic bounds. Users are logging on from India to the US, India to Australia etc.

Application Domain,Definition of SQL Server Integration Service(SSIS).

Application Domain

Application domain is nothing but a process(not exactly as normal process) where the application will be running on it. Application domain will isolates all the applications by running each application in a diff. app domain. For instance, if 5 applications are running on your system, it will then creates 5 diff app domain to guarantee the security. It also has the following advantages,

1. An application can be independently stopped.

2. An application cannot directly access code or resources in another application.

3. A fault in an application cannot affect other applications.

Definition of SQL Server Integration Service(SSIS).

SQL Server Integration Services (SSIS) is one of the main issues of Microsoft BI (Business Intelligence) concept representing the data integration and data transformation in the enterprise level approach.


SSIS (SQL Server Integration Services) has the ability to gather data from various resources in different kinds of formats, process this data, transform data and convert the processed data into any shape that you can use in your daily business as well as for data-mining and data warehouse applications.

By using SSIS, SQL Server developers and Database Administrators can reach data from XML files, text files, CSV files as well as relational databases.

By using SSIS, emailing data or many tasks like storing the processed data into a file folder can be managed easily bu not writing a line of code using the SSIS task packages.

Server hardening

What is server hardening & what needs to be done ?

Everyone,

Server Hardening is not a small part. It is always a challenge for the Server administrator. I am highlighting few points for the same.

*Run MBSA scan and fix the errors/warnings found in the result of the scan.
*TCP/IP Stack Hardening.
*System partition permission
*Disable unnecessary services
*Services should be running with the least privilege accounts
*Administrator account should be renamed.
*There should be maximum two administrative privilege accounts.
*Logging must be configured to generate failure
*All unnecessary protocols should be disabled
*Firewall should be configured

Server hardening is a process to secure your server against unknown threats. Operating system is never 100 percent secure. There are several vulnerabilities or unknown bugs come with the operating system and server services. Server hardening is a process to shut off most of all the doors from where intruders can attack or compromise the service or entire server.
It must be performed on regularly bases (Weekly/Monthly/Quarterly) since day to day there are many bugs are revealed by the master of technology.

February 24, 2015

SQL Tips-Bulk insert records into table ,Select into ,Insert into,delete record in chunks,Remove unused space

Bulk insert records into table in sql 

This is the way to Bulk insert records into table in sql or oracle

insert into 

If you want to insert of table data to existing table then you need to select insert into query.
E.g.

INSERT INTO Table2
SELECT FROM Table1

insert into dTable select * from sTable

It mean table2 and table1 both exists before execution of query and its simply insert data line by line. It takes more time on execution.



Select into:

If you want to copy of table structure with data to new table then you need to select select into query.
E.g.

Select * into tab1 from tab2.

It means tab2 exists and tab1 does not exist in database before execution of query. After execution it will copy data of tab2 to tab1 and it will create table tab1 also.


if dTable is blank and doesn't have any record.

Create this as follows: definately reduce the time.

CREATE TABLE dtable NOLOGGING AS SELECT * FROM stable


Example of delete records from sql table delete records  in chunks

If your query for deleting large number of records and its taking long time to delete records then you can try deletion by chunks. This is an simple Example of delete records from sql table in chunks

WHILE (select count(1) from Some_Table where table_sr_key !=-99 and sector_id = @some_code) >0
BEGIN
Delete top (50) from Some_Table where table_sr_key !=-99 and sector_id = @some_code
END

Remove unused space from Table in SQL

1) sp_spaceused "Tablename", @updateusage = 'TRUE'
2) DBCC UPDATEUSAGE('DBName', 'TableName')
3) DBCC CLEANTABLE ('DBName', 'TableName')
4) UPDATE STATISTICS TableName

February 22, 2015

Command to know the incoming network connections,transmitting data to server in unix

Command  to know the incoming network connections in unix 

 to know a command which would help knowing the incoming network connections through(from & to) different ip-address:ports

netstat -anp | grep "port" | grep "process"

Transmitting data to server through code in linux

int no_of_bytes_to_send;
char* buffer[100];
no_of_bytes_to_send=sizeof(buffer);
while(no_of_bytes_to_send>0)
{
int byte_send = send(sd,buffer,no_of_bytes_to_send);
no_of_bytes_to_send = no_of_bytes_to_send - byte_send;
buffer+=byte_send;


}

February 19, 2015

Code To Fill data table in DataGrid,fill static, get DataSet combo in asp.Net

Code To Fill DataGrid using asp.net 

You can try Code To Fill DataGrid using asp.net common class. After creation these methods you need to call these method to your form editors by creating class object.

Fill DataGrid
Private Sub fillgrid()
dim dta2 as New dataTable
Dim str1 As String = "select * from receipt_detail "
Dim datatb As New DataTable
datatb = obj.OpenDataTable(str1, "receipt_detail")
dta2.DataSource = datatb
dta2.DataBind()
End Sub

'Other Related Function
Public Function OpenCn() As Boolean
Try
If cn Is Nothing Then
cn = New SqlConnection
End If
If Not (cn.State = ConnectionState.Open) Then
cn.ConnectionString = constr
cn.Open()
End If
Finally
End Try
Return True
End Function

Public Function Closecn() As Boolean
Try
If Not (cn Is Nothing) Then
cn.Close()
End If
Finally
cn.Dispose()
End Try
Return True
End Function

Public Function OpenDataTable(ByVal strSQL As String, ByVal TableName As String) As DataTable
Try
OpenCn()
Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, cn)
Dim dt As DataTable = New DataTable
da.Fill(dt)
dt.TableName = TableName
Return dt
Finally
Closecn()
End Try
End Function

How to fill static combo using data table and data reader in asp dot net

Public Function FillStaticComboTrans() As DataTable
Dim dtnr As New DataTable
dtnr.Columns.Clear()
'dtnr.Columns.Add("R_NR")
dtnr.Columns.Add("ID")
dtnr.Columns.Add("Text")

Dim dr As DataRow

dr = dtnr.NewRow

dr.Item(0) = "0"
dr.Item(1) = "Select"
dtnr.Rows.Add(dr)

dr = dtnr.NewRow

dr.Item(0) = "102"
dr.Item(1) = "Opening Balances"
dtnr.Rows.Add(dr)

dr = dtnr.NewRow

dr.Item(0) = "101"
dr.Item(1) = "Deputation"
dtnr.Rows.Add(dr)
dr = dtnr.NewRow

dr.Item(0) = "103"
dr.Item(1) = "Advances"
dtnr.Rows.Add(dr)

dr = dtnr.NewRow
dr.Item(0) = "104"
dr.Item(1) = "Batch Process "
dtnr.Rows.Add(dr)

Return dtnr
End Function

Code to get DataSet using asp dot net

Dim cmd As New SqlCommand(str, objCn.OpenDB())
cmd.CommandType = CommandType.Text
Try
Dim ds As New DataSet
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds, Table)
Return ds
Catch ex As Exception
MsgBox("clsCommon::FetchData::Error occured.", MsgBoxStyle.Critical, "XyZ")
Finally
objCn.CloseDB()
cmd.Dispose()
End Try
End Function

Excecute Scalar:
Public Function Exe_Scalar(ByVal str As String, ByVal table As String) As String
Dim cd As String
Dim cmd As New SqlCommand(str, objCn.OpenDB())
cmd.CommandType = CommandType.Text
Try
cd = cmd.ExecuteScalar()
Return cd
Catch ex As Exception
MsgBox("clsCommon::FetchData::Error occured.", MsgBoxStyle.Critical, "XYZ")
Finally
objCn.CloseDB()
cmd.Dispose()
End Try

How to bind data grid using asp.net code

Dim str1 As String
Dim myDataSet As New DataSet
str1 = "select * from abc"
cn.closeDb()
Dim adp As New SqlDataAdapter(str1, cn.openDb())
adp.Fill(myDataSet, "PRICES_PLAN")

myDataSet.Tables(0).AcceptChanges()

GridView1.DataSource = myDataSet.Tables(0)
GridView1.DataBind()

February 16, 2015

Workbook Data Into One Single Sheet,Sort Alphabetic​ally ,Row column height width manipulation -MS Excel

Workbook Data Into One Single Sheet using MS Excel 

If you have 10 workbook each workbook have single sheet of data (tab name should be anything)
if you need consolidation sheet into all 10 workbook into one then you can do this with code use below mention code and your work will done .

Sub test()

Dim FS, Fle, FLDR, fles
Dim Fletype As Variant
Set FS = CreateObject("scripting.filesystemobject&quot ;)
Dim intLstrow As Integer
Dim intLstcol As Integer
Dim dlgDialoge As FileDialog
Dim srcsheet As Worksheet
Dim wk As Workbook
'Set dlgDialoge = Application.FileDialog(msoFileDialogFolderPicker)
Set wk = ThisWorkbook
Set FLDR = FS.getfolder(BrowseFolder)
Set fles = FLDR.Files
For Each Fle In fles
Fletype = Split(Fle.Name, ".")
If (Fletype(UBound(Fletype)) = "xls" Or Fletype(UBound(Fletype)) =
"xlsx") Then
Set srcsheet = Workbooks.Open(Fle.path).Worksheets(1)
intLstrow = srcsheet.Range("a" &
Application.Rows.Count).End(xlUp).Row
intLstcol = srcsheet.Range("a" &
Application.Columns.Count).End(xlUp).Column
srcsheet.Range((Cells(1, 1)), Cells(intLstrow,
intLstcol)).Copy
wk.Worksheets("BrowseFileFolders").Range ("a" &
wk.Worksheets("BrowseFileFolders").Range ("a" &
Application.Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
srcsheet.Parent.Close
End If

Next
End Sub

Public Function BrowseFolder(Optional initialPath As String = "") As String
Dim dialog As FileDialog
Set dialog = Application.FileDialog(msoFileDialogFolderPicker)
dialog.InitialFileName = initialPath
dialog.Show
If dialog.SelectedItems.Count > 0 Then
BrowseFolder = dialog.SelectedItems(1)
End If
End Function

Sort Alphabetic​ally in Tab Order for Worksheets

To Sort Alphabetic​ally in Tab Order for Worksheets in Excel you can try following vba code in your macro code editor.

 Dim nameOfSheet As String
      Dim newNameOfSheet As String
    Dim haveName As Boolean
    Dim extension As Integer
  
    haveName = False
    Do While (Not haveName)
        nameOfSheet = InputBox("Enter a sheet name")
        If checksheet(nameOfSheet) Then
            extension = 0
            If MsgBox("That name is already in use. Do you want a duplicate name?", vbYesNo) Then
                Do While (Not haveName)
                    newNameOfSheet = nameOfSheet & " (" & extension & ")"
                    If checksheet(newNameOfSheet) Then
                        extension = extension + 1
                    Else
                        haveName = True
                    End If
                Loop
            End If
        Else
            haveName = True
        End If
    Loop

Row column height width manipulation in Excel by macro code

Row column height width manipulation in Excel by macro code you can take help by following code

 Dim wb As Workbook
Dim ws As Worksheet
Dim c As Range
Dim r As Range
Dim strDescription As String

' Suppress screen updating
Application.ScreenUpdating = False

' Set workbook object
Set wb = Excel.ActiveWorkbook

' Set worksheet object
Set ws = wb.ActiveSheet

' Loop through each row
For Each r In ws.Rows.Range("1:" & xlLastCell)

' Concatenate values from cells C-L into cell M
strDescription = ""
For Each c In ws.Range("C" & r.Row & ":" & "L" & r.Row)
strDescription = strDescription + c.Value
Next c
ws.Range("M" & r.Row).Value = strDescription

Next r

' Format column M
With ws.Range("M:M")
.ColumnWidth = "60"
.WrapText = True
End With

' Clear values in columns C-L
With ws.Range("C:L")
.ColumnWidth = "1"
.WrapText = False
.Value = ""
End With

Support function in excel micro


Function ColumnRange(ByVal Filename As String, ByVal Rangehead As String) As String
Dim r As Range
Dim ic As Integer
Dim rt As String
Dim CN As String
Set r = Worksheets(Filename).Range("A1:" & LastCol(Filename) & "1")
For i = 1 To r.Count
If Rangehead = r(1, i) Then
ic = i
i = r.Count
End If
Next
If ic = 0 Then
rt = ""
Else
CN = ConvertToLetter(ic)
rt = CN & "2:" & CN & LastRow(Filename)
End If
ColumnRange = rt
End Function

Function ColumnAddress(ByVal Filename As String, ByVal Rangehead As String) As String
Dim r As Range
Dim ic As Integer
Dim rt As String
Set r = Worksheets(Filename).Range("A1:" & LastCol(Filename) & "1")
For i = 1 To r.Count
If Rangehead = r(1, i) Then
ic = i
i = r.Count
End If
Next
If ic = 0 Then
rt = ""
Else
rt = ConvertToLetter(ic)
End If
ColumnAddress = rt
End Function
Function SVlookup(ByVal destflnm As String, ByVal DestRg As String, ByVal srcflnm As String, ByVal SrcRg As String) As String

Dim RgVal As Range
'Set Value Range using filename and cell range
Set RgVal = Worksheets(destflnm).Range(DestRg)

Dim RgVlookup As Range
'Set Value Range using filename and cell range
Set RgVlookup = Worksheets(srcflnm).Range(SrcRg)

'return lookup value
SVlookup = WorksheetFunction.VLookup(RgVal, RgVlookup, 2, False)

End Function
Function secondarySVlookup(ByVal destflnm As String, ByVal primaryRG As String, ByVal secondaryRG As String, ByVal srcflnm As String) As String


Dim Rg As Range
'Set Detection range
Set Rg = Worksheets(srcflnm).Range("$A$2:$" & LastCol(srcflnm) & LastRow(srcflnm))


Dim RgPrimary As Range
'Set Primary Column Range
Set RgPrimary = Worksheets(destflnm).Range(primaryRG)


Dim RgSecondary As Range
'Set Secondary ID Range
Set RgSecondary = Worksheets(destflnm).Range(secondaryRG)

'set return string
Dim rtnstr As String
rtnstr = ""

'check each row in source worksheet
For q = 1 To LastRow(srcflnm) + 1

'if Primary Value and Secondary Value matched break
If (Rg(q, 1) = RgPrimary(1, 1) And Rg(q, 2) = RgSecondary(1, 1)) Then

'Put Value achived in return string
rtnstr = Rg(q, 3)

'break search
q = LastRow(srcflnm) + 1

End If

Next q
secondarySVlookup = rtnstr
End Function
Function LastRow(ByVal Filename As String) As Long
Dim ix As Long
ix = Worksheets(Filename).UsedRange.Row - 1 + Worksheets(Filename).UsedRange.Rows.Count
' ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastRow = ix
End Function
Function LastCol(ByVal Filename As String) As String
Dim ix As Integer
ix = Worksheets(Filename).UsedRange.Column - 1 + Worksheets(Filename).UsedRange.Columns.Count
LastCol = ConvertToLetter(ix)
End Function

Create or replace trigger before insert on field in sql

1.Create  or replace trigger before insert on field in sql

Create or replace trigger t1 Before insert on Employee

For each row

declare

v_firstname Employee.firstname%type;

v_lastname Employee.lastname%type;

Begin

DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.FIRSTNAME);

DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.LASTNAME);

Begin

select distinct firstname into v_firstname from Employee where firstname=:NEW.FIRSTNAME;

IF(:NEW.FIRSTNAME = v_firstname) THEN

DBMS_OUTPUT.PUT_LINE('This first name exists already in the table Employee.');

end if;

exception

when no_data_found

then

DBMS_OUTPUT.PUT_LINE('This first name does not exist in the table Employee.');

End;

Begin

select distinct lastname into v_lastname from Employee where lastname=:NEW.lastNAME;

IF(:NEW.LASTNAME = v_lastname) THEN

DBMS_OUTPUT.PUT_LINE('This last name exists already in the table Employee.');

end if;

exception

when no_data_found

then

DBMS_OUTPUT.PUT_LINE('This last name does not exist in the table Employee.');

End;

Begin

IF(:NEW.FIRSTNAME = v_firstname AND :NEW.LASTNAME = v_lastname)

THEN

DBMS_OUTPUT.PUT_LINE('This combination of first name and last name exists already in the table Employee.');

ELSE

DBMS_OUTPUT.PUT_LINE('This combination of first name and last name does not exist in the table Employee.');

END IF;

End;

End;

2.Example of INSERT trigger to fire upon the insert of a new record

Summary:

This is the Example of INSERT trigger to fire upon the insert of a new record and referencing table within mail. I hope you able get some idea of trigger by this example.

Details:

CREATE TRIGGER NewTestTrigger1 ON [DBO].[t_tasks_master_tasks] AFTER INSERT AS
SELECT a.Task_ID, a.Name, b.TEST
FROM t_tasks_master_tasks a, t_tasks_lu_TEST b
WHERE a.TEST_ID = b.TEST_ID
DECLARE @Name nvarchar (100)
DECLARE @TEST nvarchar (10)
SET @Name  = (SELECT Name FROM inserted)
SET @TEST = ([t_tasks_lu_TEST].[TEST])
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'New task created: ' + @Name + ' For TEST: ' + @TEST
EXEC msdb.dbo.sp_send_dbmail @xyz@gmail.com ', @body= @msg,  @subject = 'New test entered', @profile_name = 'xyzprofile'
END

3. Update status of Employee using trigger in Procedural language in sql

To Update status of Employee using trigger in Procedural language in sql you can take help of following example.

create or replace trigger emp_stat_updatetrig
 before insert or update
 on emp
 for each row
 declare
 v_sal number;
 begin
    v_sal := :new.sal;
    if v_sal < 50 then
        :new.status := 'A';
    elsif v_sal = 50 then
         :new.status := 'B';
    else
         :new.status := 'C';
    end if;
 end;

4.Code to Create trigger for insert

CREATE TRIGGER mytrig
ON s1.sh
FOR INSERT
AS
DECLARE @ModifiedDate datetime
SELECT @ModifiedDate=ModifiedDate FROM Inserted
IF (@ModifiedDate!=getdate())
BEGIN
PRINT 'HI'
END
RETURN

February 15, 2015

Select query to get Database owner and Get package name from the database in SQL

To get owner of all database in sql you can run following query in sql server query editor.
select suser_sname(owner_sid) from sys.databases

To get owner of particular database like Testdb is database name in sql you can run following query in sql server query editor.
select suser_sname(owner_sid) from sys.databases where name = 'Testdb'

 Get package name from the database query level

If you  have a front end application.When a button on that page is clicked, a package is called.If you  want to find that package name from from the db level. Db level means by writing a query.

How to find it?

use who called me function for example

OWA_UTIL.WHO_CALLED_ME(owner ,name, lineno ,caller_t ); 

Difference between primary key and unique key,Find Primary key,Index help,Not Null in SQL

Difference between primary key and unique key

A table can have only one primary key but multiple unique keys.
unique key may have null value which is not allowed in primary.
simply primary key is unique + not null where as unique key Field contains any number of NULL values.


Find primary key, index_name ,index key  using sp_Help command in sql

How to find primary key, index_name and index_key in the same query

For a particular table and also want to group by index_name
use sysindexes table to get the output you want

use the stored procedure sp_helpindex [object name]

sp_help 'table name'

Null and Not Null value in Primary and unique keys in sql

Primary key should contain unique value and not null because if attribute value is null than how should maintain the uniqueness..

one more think i want to add that Primary key is a type of constraint and foreign key is also a type of
constraint and pk is null then u can't use the null value as a foreign key.
So, Primary key should be a unique value and not null.

Well first of all null is not a value its a state, null is undefined , so we cannot have null as primary key as we do not know its value also value of one null is different from another null, so if we allow null values each null is unique in a sense , but since we do not know what it stands for we do not allow it in primary key.

Though in case of foreign key we do allow null value reason being
a child(foreign) cannot point towards an non existent parent(primary key) but a child can be orphan it self hence we can allow null in foreign key.

As primary key column is the candidate column in the table where the data is going to be identified by this column itself so it should have a value and the unique value .

Primary key is a unique key which should not accept any null values.& it doesn't allow any duplicates also.whenever table is created the first record will be filled with null values. whenever i create a primary key constraint on table internally an unique index should be created.which doesn't allow any nulls.Null is a missing or unknown value

Simple command to shrink all database at once to reduce database size in SQL server

Simple command to shrink all database at once to reduce database size in SQL server

This is very simple command to shrink all database at once to reduce database size in SQL server but one thing you need to remember do not forget to make recovery mode simple before executing this command.


EXEC sp_MSForEachDB 'select ''?'' as [Database]; ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE (''?'' , 0)'


After execution of command it will display all database name with details of size. I hope this command will help you a lot on troubleshooting of disk size.

Shrinking and reducing database size in sql

Summary:

If there is big difference in ldf file and .bak file then you can shrink database to reduce size of database. you can use following method at

Details

use <dbname>
checkpoint
go
backup log <dbname> with truncate_only,
go
dbccshrinkfile(<logfilename>,1000)

Re-indexes all the tables using DBCC BREINDEX command

Summary:
re-indexes all the tables using DBCC BREINDEX command.
This goes and re-indexes all the tables and takes about 7 minutes and make an improvement of 10%

Details:

USE DbName_1 --Enter the name of the database you want to reindex totally
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
SELECT YourTableName FROM information_schema.tables 
WHERE table_type = 'base table' 
OPEN TableCursor 
FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
DBCC DBREINDEX(@TableName,' ',100) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
CLOSE TableCursor 
DEALLOCATE TableCursor


ReIndexing All Database Tables at once in SQL Database Server

This is a simple three line command for Re-indexing All Database Tables at once in SQL Database Server. This command is very helpful to improve your database performance by re indexing tables. Its Update Statistics on Tables.

USE YouDatabaseName
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

February 10, 2015

SQL Stored Procedure Examples-Create,Delete and Drop Table ,insert,row count

Create,Delete and Drop Table  Through SQL Stored Procedure

Following is code which you can create new Stored procedure in SQL Stored procedure section and can write following code. You can understand easily by reviewing whole code.

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

Create PROCEDURE [dbo].[SP_abc](@userID char(3)) AS
BEGIN
-- Statement to create table
if NOT exists (select * from dbo.sysobjects where id = object_id('abc'))
exec("CREATE TABLE abc(@field1 CHAR(3),LOGDT SMALLDATETIME,LOGTM DATETIME )")
END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Statement to Delete records from table table
Create PROCEDURE [dbo].[SP_DELETE](@ID1 char(3)) AS
BEGIN
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id('L'))
BEGIN
DELETE FROM abc WHERE ID1=@id1
END
END

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
-- Statement to Drop table
Create PROCEDURE [dbo].[DropTable1](@ID1 char(3)) AS
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@Id1))
EXEC("DROP TABLE " + @ID1)
END

As mention variable with @ sign mean its parameter which you can pass value at run time.

Stored Procedure example to get Total Rows using query

This is a Stored Procedure to get Total Rows using select  query in SQL . You can use count method to get value of record count form table. You can specify parameter in query. For example how to get total number of rows which is code 0001 etc.


Set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[ COUNT_ROWS1](@CD CHAR(2))
AS
SELECT COUNT(*) AS TOTROW FROM HEAD1 WHERE LEFT(NO,2)=@CD

This will count row as per given cd no.

 Stored Procedure example to Insert values in tables

You can write following lines to create Stored Procedure to Insert values in tables in sql.

Create PROCEDURE [dbo].[SP_INSERT_STATE]
(
@STATE_CD1 CHAR(3),
@STATE_NAME1 CHAR(22),
@COUNTRY_CD1 CHAR(4),

)
AS
INSERT INTO STATE VALUES(@STATE_CD,@STATE_NAME,@COUNTRY_CD)
RETURN

In above statements (@STATE_CD,@STATE_NAME,@COUNTRY_CD is Parameter which can be passed at run time.

February 9, 2015

SQL Join Query Tips-Example of right ,self query itselft,anti ,left join,Full outer join


Example of self join query in sql.

Summary :

If you are looking for information like self join query mean table used self as join let us see following example to get more clear.

Hear I used  right join with self join.

Example of self join query in sql,right join,self join,join query itself.

select * from emp where empno not in (select e.empno from emp e right join dept d on e.empno = d.empno)

How to use self join in sql select query

Example of self join:

To find the IDs which have a different department:

select distinct A.id  from myTable A join myTable B on A.id = B.id
where A.dept <> B.dept

Example of join select query in sql

Query to get a list of all students who have a certain checklist item (MFST) with a status of Initiated where all other checklist items have a status of Completed. Should I be using Exists or should I be using someting else?

SELECT A.COMMON_ID, 
       B.AID_YEAR, 
       C.CHKLST_ITEM_CD, 
       C.ITEM_STATUS
FROM PS_PERSON_CHECKLST A
JOIN PS_VAR_DATA_FINA B   ON (B.VAR_DATA_SEQ = A.VAR_DATA_SEQ AND B.COMMON_ID = A.COMMON_ID)
JOIN PS_PERSON_CHK_ITEM C ON (C.SEQ_3C = A.SEQ_3C AND C.COMMON_ID = A.COMMON_ID)
WHERE A.INSTITUTION = :1
  AND B.AID_YEAR = '2013'
  AND (C.CHKLST_ITEM_CD LIKE 'MFST%' AND C.ITEM_STATUS = 'I')
   OR (C2.CHKLST_ITEM_CD NOT LIKE 'MFST%' AND C2.ITEM_STATUS = 'C');

Example of left join in sql

This is simple Example of left join in sql. You can try with your tables to see result of query.

select stuff  from folks 
join (select fkey from these events ) yummy on folks.key = yummy .fkey 
left join (select fkey from those events) yuck on folks.key = yuck.fkey 
where yuck.fkey is null 

Anti join operation and left join in sql

 The NOT IN operator (an anti-join operator) is more efficient than a LEFT JOIN...IS NULL construction because:

 The LEFT JOIN produces a row for each left-side row, extending it as needed with NULL value placeholders. The resulting row is then evaluated for the NULL value.

 The NOT IN operator performs an equi-join and produces a result row only when the equi-join fails. There is no post-join predicate to evaluate.

 The cost of the LEFT JOIN plan includes the join and (the predicate evaluation X left-side rows). The cost of the NOT IN plan is the join alone.

Example of full outer join in sql

This is Example of full outer join in sql

select
isnull(c.CPTCode ,p.CPTCode) as CPTCode
, c.mtd
, p.TotalCharges
, p.TotalUnits
from #C c full outer join #Prod p
on c.grpType = p.grpType
and c.CPTCode = p.CPTCode

Which produced the following result

CPTCode mtd TotalCharges TotalUnits
76800 1321.61 6100.00 12
76856 246.01 NULL NULL
76881 9778.95 38749.00 81
76942 22467.33 54733.00 114
76880 NULL -475.00 -1

Whate is Java Servlets ,JDBC,JAVA API,Webserver


WHAT IS JAVA SERVLETS ?

An HTTP servlet can generate an HTML page, either when the servlet is accessed explicitly by name, by following a hypertext link, or as the result of a form submission. An HTTP servlet can also be embedded inside an HTML page, where it functions as a server-side include. Servlets can be chained together to produce complex effects--one common use of this technique is for filtering content. Finally, snippets of servlet code can be embedded directly in HTML pages using a new technique called JavaServer Pages.


Servlets : A servlet is dynamically loaded module that services requests from Web server, by another service called response. Servlet is efficient as it is initialized only once when the web server loads it.
Servlets are efficient, persistent, portable, robust, extensible, and secure, and they have facility to embed JavaScript.

JDBC :  The JDBC is a pure JAVA API used to execute SQL statements.
It provides the classes and interfaces that can be used by developers to write     database applications. Basic JDBC interaction, its simpler form is divided into four steps:
 1. Open a connection to the database.
 2. Execute a SQL statement.
 3. Process the result. 
4. Close the connection to the database.


The servlet API:

Through Servlet API we use to create HTTP servlets and any other kind or servlets. Servlet use classes and interfaces from two packages: javax.servlet and javax.servlet.http.
The javax.servlets contains classes to support generic , protocol-independent servlets. These classes are extended by the classes in the javax.servlrt.http packages to add HTTP specific functionality.

Anti Virus Tips-Remove autorun.inf folder virus,protect from virus,Best Antivirus

Remove autorun.inf folder virus

1. click on Start Button
2.then Click on Run
3. write down "cmd" in the Run box then you can see a CMD box.

Here, you will write down cd...(Change directory)
you can see like this C:\>
then write c:\> attrib
when you will write this command "attrib" it will show you all of your affected file.
but you should only attentive for SHR autorun.inf and SHR AUTOTEXT.bat(these are virus)
then follow this instruction>>>
C:\>attrib -s -h -r autorun.inf
C:\> del autorun.inf

and then restart your computer. if you see this problem again then just delete autorun.inf. you should do same thing for all of your drivers.

Some tips to protect from virus

Here are some practical tips to help you decide whether or not to open an attached emails:

1. If you get an email with an attachment from someone you don't know, delete it. You don't take candy from strangers, and you should behave the same with email attachments.


2. If you get an email with an attachment from a friend, don't assume it's harmless! Many viruses spread by automatically sending themselves to the addresses found in the victim's address book, and they often include something in the message body that looks like a personal message from your friend.


3. Unless you are very computer savvy, and you can tell for sure from the name of the attached file that it's not a virus, then CALL or EMAIL your friend and ask if they meant to send you an attachment.


4. If they say yes, and they can explain what it is (photos of the family picnic, etc.) it should be safe to open the attachment.


5. If they say no, then obviously you should delete the message and let them know they might be infected with a virus. It's also quite likely that the virus didn't come from your friend at all. Many viruses spoof the "From" address in the emails they spew out, so it's hard to learn the true origin.



Which antivirus is best for Internet ?

Free AV :

1. Avira (free) - Damn fast, great detection level, not resource hungry, updates are not that smooth, annoying ad pop-up @ each update.

2. Avast Home - Integrated antispyware, good detection level, fast updates, slow during deep scans, some false positives, resource hungry with lot of (unwanted ?) options.

Paid :
1. Nod 32 - Fast, great detection level, a bit resource hungry
2. KAV - Great detection level with annoying false positives, kind of cheap in comparison with Nod, may block a lot of applications with/without notification. Simply, overkill for a normal user.
* Then there are Trend/Quick-heal/Norton/Panda.. Not much of a fan of McAfee

SQL Select Query Examples-retrieve Last five records,subquery , last letters in capital

Select Query to retrieve Last five records of the table

select * from emp a where 5 >= (select count(1) from emp b where a.empno != b.empno and a.sal <= b.sal)
order by a.sal desc

assumptions:
1. Empno is primary key in emp table
2. 5 last records must be required based on some sorting, so i took it as salary.

Select Query to get the first and last letters in capital in SQL

This is sample example to use Select Query to get the first and last letters in capital in SQL.

You can use with your table instead of test5. upper is function to get letter in upper case and substr method is used to get first and last letter.

select upper(substr(ename,1,1)) || substr(ename,2,length(ename)-2) || upper(substr(ename,length
(ename),1)) from test5;

Example of subquery in sql

select e.employee_id,e.first_name||' '||last_name as name,e.department_id,e.hire_date,e.manager_id,d.department_name,
m.first_name||' '||m.last_name manager from employees e join departments d
on e.department_id=d.department_id left outer join emp m
on e.manager_id=m.employee_id
where d.department_name=initcap('shipping') order by e.employee_id;

Select query to get range of Records:

To get range of records in sql you can try following select query. In this query num1 and num2 is a query parameters. Using minus will filter records  from first table. It will show non common  rows from tables.

select * from table_name where rowid in (select rowid from table_name
where rownum < &num1 minus select rowid from table_name
where rownum < &num2