Association Table - Create UserRoles Association Table for Users and Roles Table

An association (mapping) table, also called a link table or join table, is a type of table in a relational database that is used to associate(map) records from two or more other tables. Typically used in many-to-many relationships. In this case, any record in one table can be related to multiple records in another table and vice versa.

 An association (mapping) table typically contains a foreign key that references the primary key of the table to which it is mapped. 

Example of an association (mapping) table -

"usersRoles" table have two columns: userId and roleId. This table is used to associate "users" table with "roles" table in a many-to-many relationship. The primary key is a composite key made up of both columns (PRIMARY KEY(userId, roleId)), and there are foreign key constraints to ensure that the "userId" column references the "id" column in the "users" table and the "roleId" column references the "id" column in the roles table.

Depends on the role assigned to user user will be able access or perform the actions.

Please refer below SQL Statement,

 CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

CREATE TABLE usersRoles (
    userId INTEGER NOT NULL,
    roleId INTEGER NOT NULL,
    PRIMARY KEY(userId, roleId),
    FOREIGN KEY(userId) REFERENCES users(id),
    FOREIGN KEY(roleId) REFERENCES roles(id)
);


-- Inserting data into the users table
INSERT INTO users (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'password123'),
       ('Jane Smith', 'janesmith@example.com', 'mypassword'),
       ('Bob Johnson', 'bobjohnson@example.com', '123456');

-- Inserting data into the roles table
INSERT INTO roles (name, description)
VALUES ('Admin', 'Has full access to the system.'),
       ('Editor', 'Can edit and create content.'),
       ('Viewer', 'Can view content but cannot make changes.');
      
-- Inserting data into the usersRoles table
INSERT INTO usersRoles (userId, roleId)
VALUES (1, 1), -- John Doe is an Admin
       (2, 2), -- Jane Smith is an Editor
       (3, 3), -- Bob Johnson is a Viewer
       (1, 2), -- John Doe is also an Editor
       (2, 3); -- Jane Smith is also a Viewer

Read More

LINQ-To-SQL Vs Stored Procedures?

Advantages of LINQ Vs Stored Procedures :

Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls Stored Procedures.

Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).

Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).

Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.

Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. 

Disadvantages of LINQ Vs Stored Procedures :

Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.

Less flexible: Sprocs can take full advantage of a database's feature set. LINQ tends to be more generic in it's support. This is common in any kind of language abstraction (e.g. C# vs assembler).

Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

Security and manageability are something that people argue about too.

Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict the direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).

Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.

Read More

SQL Profiler

In this post, I will explain about SQL Profiler.

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Read More

How to delete duplicate rows using CTE in SQL Server?

In this post, I will explain How to delete duplicate rows using CTE in SQL Server.

A CTE stands for Common Table Expression. A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE always returns a result set.

 In below example, I will explain the use of CTE to delete duplicate rows from a table. In an example, I have table tbl_Student with two columns Name & BirthDate. In the table, tbl_Student have some duplicate rows. To achieve this I used CTE with SQL function ROW_NUBER() over partition by Name & BirthDate.

Table tbl_Student SQL Script

CREATE TABLE tbl_Student(

            Name varchar(100) NULL,

            BirthDate date NULL

)

  

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1996-03-08' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

Image 1 shows a table with data.

With CTE AS  

( 

SELECT          Name, BirthDate, ROW_NUMBER() OVER (PARTITION BY Name, BirthDate ORDER BY Name, BirthDate) AS SeqNo

FROM tbl_Student

) 

DELETE FROM CTE WHERE SeqNo > 1 ;

Image 2 shows a result set CTE.

 Image 3 shows the table after deleting duplicate rows.

Read More

How to get @@ERROR and @@ROWCOUNT at the same time?

In this post, I will explain How to get @@ERROR and @@ROWCOUNT at the same time?

If @@ROWCOUNT is checked after Error checking statement, then it will have 0 as the value of @@ROWCOUNT will get reset. And if @@ROWCOUNT is checked before the error-checking statement then @@ERROR will get reset.

1) In this case @@ERROR will be “0”

SELECT @RC = @@ROWCOUNT

SELECT @ER = @@ERROR

2) In this case @@ROWCOUNT will be “0”

SELECT @ER = @@ERROR

SELECT @RC = @@ROWCOUNT

To get @@ERROR and @@ROWCOUNT at the same time do both in same statement and store them in local variable.

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Read More

SQL SubQuery

A SubQuery is a query in a query. SQL subquery is usually added in the WHERE clause of the SQL statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database.

Subqueries are an alternate way of returning data from multiple tables. A subquery is also known as Inner Query & Nested Query.

Subqueries can be used with the following SQL statements along with the operators like =, <, >, >=, <=, LIKE IN, NOT IN etc.

SELECT

INSERT

UPDATE

DELETE

 A SubaQuery mostly used perform following tasks,

• Check whether the query selects any rows.
• Compare an expression to the result of the query.
• Determine if an expression is included in the results of the query.

Example of Subquery

Subqueries with SELECT statement

Subquery usually returns only one record, but it can also return multiple records when used with operators LIKE IN, NOT IN in the where clause. The query syntax would be like,

SELECT Id, Name 

FROM Student

WHERE Id IN 

 (SELECT Id FROM Student_Marks WHERE Marks >= 75); 

Subqueries with INSERT statement

INSERT statement can be used with subqueries. Here is an example of subqueries using INSERT statement.

INSERT INTO Student_Subject (Id, Subject)

SELECT Id, 'Maths' AS Subject

FROM Student

WHERE Id IN 

 (SELECT Id FROM Student_Marks WHERE Marks >= 75); 

Subqueries with an UPDATE statement

UPDATE statement can be used with subqueries, you can set new column value equal to the result returned by a single row subquery. Here is an example of subqueries using an UPDATE statement.

UPDATE Student

SET Grade='A'

WHERE  Id IN 

 (SELECT Id FROM Student_Marks WHERE Marks >= 75); 

Subqueries with a DELETE statement

DELETE statement can be used with subqueries. Here is an example of subqueries using a DELETE statement.

DELETE FROM Student

WHERE  Id IN 

 (SELECT Id FROM Student_Marks WHERE Marks < 35);

Read More

Local and Global temporary tables in SQL Server

In this post, I will explain Local and global temporary tables in SQL Server.

1. A local temporary table exists only for the duration of a connection or if defined inside a compound statement, for the duration of the compound statement. Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash ("#") sign.

Example-

CREATE TABLE #LocalTemp

(

UserID int,

Name varchar(50),

Address varchar(150)

)

GO

insert into #LocalTemp values ( 1, 'Name','Address');

GO

Select * from #LocalTemp

Drop table #LocalTemp

The scope of Local temp table exists to the current session of a current user means to the current query window. If you will close the current query window or open a new query window and will try to find above-created temp table, it will give you the error.

2. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When the connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when the database is opened next time.

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign.

Example-

CREATE TABLE ##GlobalTemp

(

UserID int,

Name varchar(50),

Address varchar(150)

)

GO

insert into ##GlobalTemp values ( 1, 'Name','Address');

GO

Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Read More

How to find second highest salary in SQL Server?

In this post, I will explain How to find second highest salary in SQL Server table?

Find the second highest salary is a common interview question for many peoples. There are different ways to get a second highest salary using SQL Query. Here I will explain a few of them with below example.

Example-

Consider below simple table Employee.

Name	Salary
A	100,000.00
B	1,000,000.00
C	40,000.00
D	500,000.00

In the above table, D has the second highest salary. Finding the highest salary using SQL query is very easy.
SELECT MAX(Salary) as salary FROM Employee

We can nest the above query to find the second highest salary.

SELECT MAX(Salary) AS salary
FROM Employee

WHERE Salary < (SELECT MAX(Salary) FROM Employee)

Below is another way to find second highest salary.

SELECT  Salary
FROM    Employee
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY

Read More