Welcome Everybody,
It's very long time to posting you a very new and fresh topic, Sorry for delayed guys I was very busy with tight schedule.
So here I am going to discuss about Exist instead of Count. Have a brief below, let me know your suggestion and feedback on same. Thanks for being patient..
Instead of using Count to
check whether record(s) exist or not in your table while you are checking for
example whether user exist or not. If you are using below query:
SELECT COUNT(*) FROM UserMaster
IT will scan all the tables
with relevent column and gives you the result whehter record exist or not
(which is not useful).It’s betterto use EXISTS clause to give faster result.
Here is a complete example:
--Create Table UserMaster
CREATE TABLE [dbo].[UserMaster](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NOT NULL,
[Password] [varbinary](50) NULL,
[Active] [bit] NULL,
[CreationDate] [datetime] NULL,
CONSTRAINT
[PK_UserMaster] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[UserID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Insert values into Table UserMaster
INSERT INTO
UserMaster (UserID, Password, Active) VALUES ('Admin',Convert(varbinary(50),'admin@123'),1)
--CREATE Stored Procedure to check User
--Check Active User
--uspCheckUser 'admin','admin@123'
ALTER PROCEDURE uspCheckUser
@UserID AS VARCHAR(50),
@Password AS VARCHAR(50)
AS
BEGIN
SELECT ID, UserId FROM
USerMaster WHERE EXISTS (SELECT ID, UserId FROM USerMaster WHERE
UserID = @UserID AND
Password =
@Password)
END
--And Finally Run Stored Procedure
exec
uspCheckUser 'admin','admin@123'
Comments