Joins in SQL

Dear Friends,

Some time we got problems in views , when we run default views it shows all records from both the table, if we want only records from one table always use RIGHT JOIN or LEFT JOIN
it will shows only one table record.

if you want to show all records (Not only matching records) use Right join or Left Join

Sample View

SELECT dbo.view_DistCommission.

inttransectioncode AS Expr3, dbo.mstreport.ReferenceNo, dbo.mstreport.MerchantID AS Expr2,
dbo.view_DistCommission.intrdsbooking, dbo.view_DistCommission.transectiondate, dbo.view_DistCommission.distributorcode,
dbo.view_DistCommission.customername, dbo.view_DistCommission.strstatus, dbo.view_DistCommission.createddatetime,
dbo.view_DistCommission.Expr1, dbo.view_DistCommission.strAgencyName, dbo.view_DistCommission.b2cdistributortac,
dbo.view_DistCommission.b2cagenttac, dbo.view_DistCommission.transectionstatus, dbo.view_DistCommission.offerprice,
dbo.view_DistCommission.agentcode, dbo.view_DistCommission.publishedprice, dbo.view_DistCommission.Description,
dbo.view_DistCommission.product, dbo.view_DistCommission.strmerchantcode, dbo.view_DistCommission.merchantid,
dbo.view_DistCommission.TDS, dbo.view_DistCommission.inttransectioncode
FROM dbo.view_DistCommission LEFT OUTER JOIN
dbo.mstreport ON dbo.view_DistCommission.inttransectioncode = dbo.mstreport.strtransectionid
GROUP BY dbo.view_DistCommission.inttransectioncode, dbo.mstreport.ReferenceNo, dbo.mstreport.MerchantID, dbo.view_DistCommission.intrdsbooking,
dbo.view_DistCommission.transectiondate, dbo.view_DistCommission.distributorcode, dbo.view_DistCommission.customername,
dbo.view_DistCommission.strstatus, dbo.view_DistCommission.createddatetime, dbo.view_DistCommission.Expr1,
dbo.view_DistCommission.strAgencyName, dbo.view_DistCommission.b2cdistributortac, dbo.view_DistCommission.b2cagenttac,
dbo.view_DistCommission.transectionstatus, dbo.view_DistCommission.offerprice, dbo.view_DistCommission.agentcode,
dbo.view_DistCommission.publishedprice, dbo.view_DistCommission.Description, dbo.view_DistCommission.product,
dbo.view_DistCommission.strmerchantcode, dbo.view_DistCommission.merchantid, dbo.view_DistCommission.TDS

Thanks
Pradeep Deokar

Dear Friends

Here you will get all information of your database using this query
try this

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME


Hope it is helpfull to you.

Count Totals & Sub Totals in Sql SERVER

The SQL Server ROLLUP operator is useful in generating reports that contain subtotals and totals.

Here’s how to use the ROLLUP operator in the Northwind database to give a breakdown of the Orders placed by each Customer as well as the Total number of Orders placed

select ISNULL(intagentid,0) Agent,
count(intagentid) as distributors
from mst_Login where strtype ='Distributor '
group by intagentid with ROLLUP

count & union Example

Recently My friend has some requirement

he want all records from table where status = single and count of records where status = bulk

it is simple in programming , we both do some R&D at last we got the solution

here is the solution

select intagentid,strtype from mst_Login where status = 'single' Union
Select count(*) as intagentid,strType from mst_login where status = 'bulk' group by strType

using this query we get all recrods of single & count of bulk

Using union in sql basic rule is u have to select same number of column in both queries


Thanks
Pradeep Deokar

Use Inbuilt Store Procedure for delete,drop,truncate all tables from one database

Hi Friends ,

Sometime we need to delete all data from all tables from one database ,
so instead of deleting all tables manually , Here is the solution

EXEC sp_MSforeachtable @command1 = "Delete ?"

You can use this Query in multiple ways depending on your requirement

for ex:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

As sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

try this, it will clear your comcepts

EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

It will delete all data from all tables from single database ,
so we will get blank database

Some time programmer test there application by inserting dummy data into tables
after finishing testing it will live on server so before getting live we can remove all dummy data from all table in one query

Hope it is helpfull for u

Thank You
Pradeep Deokar

User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

• UDF has No Access to Structural and Permanent Tables.
  • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
• UDF Accepts Lesser Numbers of Input Parameters.
  • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
• UDF Prohibit Usage of Non-Deterministic Built-in Functions
  • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
• UDF Returns Only One Result Set or Output Parameter
  • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• UDF can not Call Stored Procedure
  • Only access to Extended Stored Procedure.
• UDF can not Execute Dynamic SQL or Temporary Tables
  • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
• UDF can not Return XML
  • FOR XML is not allowed in UDF
• UDF does not support SET options
  • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• UDF does not Support Error Handling
  • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

convert varchar to int in sql

Few of the questions I receive very frequently. I have collect them search it & here is some important one's

How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.