Home
Search results “Blocking select oracle”
Structure of Data Blocks - DBArch Video 29
 
05:35
In this video you will understand about the contents of a Data Block. You will learn about Block Header, Transaction slots, Row Directory and space for Data / rows. . Our Upcoming Online Course Schedule is available in the url below https://docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY/edit#gid=0 Reach us at [email protected]
Views: 5037 Ramkumar Swaminathan
Oracle Locks Explained Part 1
 
12:02
Oracle Locks explained. How to Kill a User session in oracle database- Neway IT Solutions
Views: 2177 NewayITSolutions LLC
Difference between blocking and deadlocking
 
06:52
deadlock vs blocking sql server In this video we will discuss the difference between blocking and deadlocking. This is one of the common SQL Server interview question. Let us understand the difference with an example. SQL Script to create the tables and populate them with test data Create table TableA ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableA values ('Mark') Go Create table TableB ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableB values ('Mary') Go Blocking : Occurs if a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock. Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that Transaction 2 is blocked by Transaction 1. Transaction 2 is allowed to move forward only when Transaction 1 completes. --Transaction 1 Begin Tran Update TableA set Name='Mark Transaction 1' where Id = 1 Waitfor Delay '00:00:10' Commit Transaction --Transaction 2 Begin Tran Update TableA set Name='Mark Transaction 2' where Id = 1 Commit Transaction Deadlock : Occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward. Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that there is a deadlock between Transaction 1 and Transaction 2. -- Transaction 1 Begin Tran Update TableA Set Name = 'Mark Transaction 1' where Id = 1 -- From Transaction 2 window execute the first update statement Update TableB Set Name = 'Mary Transaction 1' where Id = 1 -- From Transaction 2 window execute the second update statement Commit Transaction -- Transaction 2 Begin Tran Update TableB Set Name = 'Mark Transaction 2' where Id = 1 -- From Transaction 1 window execute the second update statement Update TableA Set Name = 'Mary Transaction 2' where Id = 1 -- After a few seconds notice that one of the transactions complete -- successfully while the other transaction is made the deadlock victim Commit Transaction Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-blocking-and.html
Views: 75214 kudvenkat
PL/SQL tutorial 1: PL/SQL Block Types in Oracle Database By Manish Sharma
 
02:47
PL/SQL Tutorial 1 Answering The Questions What are Blocks in PL/SQl and What are different Sections Such as Declaration, Execution and Exception handling In Pl/SQL ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/block-types ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! -------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 339885 Manish Sharma
sql query processing in oracle database
 
05:01
sql query processing in oracle database
MSSQL - Understanding Isolation Level By Example (Serializable)
 
08:46
Example SQL Statements below used in the video, you can Copy and Paste for Transaction Isolation Level of Serializable, Read Committed, Read Uncommitted, Repeatable Read --===================================== -- Windows/Session #1 --===================================== SELECT @@SPID IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'SampleTable') DROP TABLE SampleTable CREATE TABLE [SampleTable] ( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NULL, [Value] [varchar](100) NULL, [DateChanged] [datetime] DEFAULT(GETDATE()) NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id] ASC) ) INSERT INTO SampleTable(Name, Value) SELECT 'Name1', 'Value1' UNION ALL SELECT 'Name2', 'Value2' UNION ALL SELECT 'Name3', 'Value3' SELECT * FROM SampleTable BEGIN TRAN INSERT INTO SampleTable(Name, Value) VALUES('Name4', 'Value4') --UPDATE SampleTable SET Name = Name + Name --UPDATE SampleTable SET Name = Name + Name WHERE Name = 'Name1' UPDATE SampleTable SET Name = Name + Name WHERE ID = 2 DELETE FROM SampleTable WHERE ID = 4 WAITFOR DELAY '00:0:10' COMMIT TRAN --===================================== -- Windows/Session #2 --===================================== --------------------------------------------------- -- This window/session is default READ COMMITTED -- --------------------------------------------------- SELECT @@SPID BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable ROLLBACK SELECT b.name, c.name, a.* FROM sys.dm_tran_locks a INNER JOIN sys.databases b ON a.resource_database_id = database_id INNER JOIN sys.objects c ON a.resource_associated_entity_id = object_id --===================================== -- Windows/Session #3 --===================================== ----------------------------------------------------- -- This window/session is REPEATABLE READ -- ----------------------------------------------------- SELECT @@SPID SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable COMMIT TRAN --===================================== -- Windows/Session #4 --===================================== ----------------------------------------------------- -- This window/session is SERIALIZABLE -- ----------------------------------------------------- SELECT @@SPID SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable COMMIT TRAN
Views: 13473 CodeCowboyOrg
Oracle Locks Part2  Killing a User Session
 
12:46
Oracle Locks Part 2- Killing a User session- Neway IT Solutions
Oracle Locks and Lock Trees
 
07:08
Rows Locks and sessions waiting in a "tree order" on Row Locks in Oracle
Views: 229 Hemant K Chitale
Does SQL table join order matter?
 
14:10
Have you ever wondered if the order of the tables in your FROM clause matter? In today's video, we look at why theoretically table join order *does* make a difference for performance and why SQL Server doesn't use the order you provide. We also look at different techniques for forcing SQL Server to use the join order you specified. Blog post with all demo code and links: https://bertwagner.com/2017/11/21/does-the-join-order-of-my-tables-matter/ Presentation by Adam Machanic on row goals: https://sqlbits.com/Sessions/Event14/Query_Tuning_Mastery_Clash_of_the_Row_Goals Music by Joakim Karud http://youtube.com/joakimkarud Follow me on Twitter: https://twitter.com/bertwagner Want to receive my latest weekly blog posts and videos in your inbox? Sign up for the newsletter here: https://upscri.be/c77fc8/
Views: 2035 Bert Wagner
How To Fix -Application Blocked By Java Security Error  In Java 8?
 
05:35
If you guys want high-quality, and more FREE tech content, You can Support me on Patreon and get some great rewards! https://www.patreon.com/TreeAcademy How To Fix -Application Blocked By Java security error ? 1. If you are running Java 8 than you have to uninstall Java 8 By going to Control panel as there is no way to select Medium security in Java 8. 2. Go to http://google.com and do a search for "Java 7 download" and go to Site and Download Java 7 . 3. Install Java 7 in your computer . 4. Add Your Application list URL to the exception site list and you will be able to run your Application easily . Hope This fixes your issue. Thanks for watching,if you like this tutorial ,please like and share . ============================================== Facebook: https://www.facebook.com/GoTreeAcademy Google+ : https://goo.gl/zAAXK6 Twitter: https://twitter.com/gotreeacademy #howto #fixApplicationblocked #javasecurityerror #java8
Views: 211236 Tree Academy
SQL Server except operator
 
05:14
sql except example sql except operator example sql server except example sql server except order by sql server except clause SQL Server except operator In this video we will discuss SQL Server except operator with examples. EXCEPT operator returns unique rows from the left query that aren’t in the right query’s results. Introduced in SQL Server 2005 The number and the order of the columns must be the same in all queries The data types must be same or compatible This is similar to minus operator in oracle Let us understand this with an example. SQL Script to create the tables Create Table TableA ( Id int primary key, Name nvarchar(50), Gender nvarchar(10) ) Go Insert into TableA values (1, 'Mark', 'Male') Insert into TableA values (2, 'Mary', 'Female') Insert into TableA values (3, 'Steve', 'Male') Insert into TableA values (4, 'John', 'Male') Insert into TableA values (5, 'Sara', 'Female') Go Create Table TableB ( Id int primary key, Name nvarchar(50), Gender nvarchar(10) ) Go Insert into TableB values (4, 'John', 'Male') Insert into TableB values (5, 'Sara', 'Female') Insert into TableB values (6, 'Pam', 'Female') Insert into TableB values (7, 'Rebeka', 'Female') Insert into TableB values (8, 'Jordan', 'Male') Go Notice that the following query returns the unique rows from the left query that aren’t in the right query’s results. Select Id, Name, Gender From TableA Except Select Id, Name, Gender From TableB To retrieve all of the rows from Table B that does not exist in Table A, reverse the two queries as shown below. Select Id, Name, Gender From TableB Except Select Id, Name, Gender From TableA You can also use Except operator on a single table. SQL script to create tblEmployees table Create table tblEmployees ( Id int identity primary key, Name nvarchar(100), Gender nvarchar(10), Salary int ) Go Insert into tblEmployees values ('Mark', 'Male', 52000) Insert into tblEmployees values ('Mary', 'Female', 55000) Insert into tblEmployees values ('Steve', 'Male', 45000) Insert into tblEmployees values ('John', 'Male', 40000) Insert into tblEmployees values ('Sara', 'Female', 48000) Insert into tblEmployees values ('Pam', 'Female', 60000) Insert into tblEmployees values ('Tom', 'Male', 58000) Insert into tblEmployees values ('George', 'Male', 65000) Insert into tblEmployees values ('Tina', 'Female', 67000) Insert into tblEmployees values ('Ben', 'Male', 80000) Go Except operator on a single table Select Id, Name, Gender, Salary From tblEmployees Where Salary ]= 50000 Except Select Id, Name, Gender, Salary From tblEmployees Where Salary ]= 60000 Order By clause should be used only once after the right query Select Id, Name, Gender, Salary From tblEmployees Where Salary ]= 50000 Except Select Id, Name, Gender, Salary From tblEmployees Where Salary ]= 60000 order By Name Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-except-operator.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/adsbygoogle-window.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 42032 kudvenkat
Difference between rows and range
 
05:52
range vs rows in sql server difference between rows clause and range clause in sql server range clause vs rows clause in sql server sql server running total query running total example in sql server In this video we will discuss the difference between rows and range in SQL Server. This is continuation to Part 116. Please watch Part 116 from SQL Server tutorial before proceeding. Let us understand the difference with an example. We will use the following Employees table in this demo. SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Salary int ) Go Insert Into Employees Values (1, 'Mark', 1000) Insert Into Employees Values (2, 'John', 2000) Insert Into Employees Values (3, 'Pam', 3000) Insert Into Employees Values (4, 'Sara', 4000) Insert Into Employees Values (5, 'Todd', 5000) Go Calculate the running total of Salary and display it against every employee row The following query calculates the running total. We have not specified an explicit value for ROWS or RANGE clause. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS RunningTotal FROM Employees So the above query is using the default value which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW This means the above query can be re-written using an explicit value for ROWS or RANGE clause as shown below. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees We can also achieve the same result, by replacing RANGE with ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees What is the difference between ROWS and RANGE To understand the difference we need some duplicate values for the Salary column in the Employees table. Execute the following UPDATE script to introduce duplicate values in the Salary column Update Employees set Salary = 1000 where Id = 2 Update Employees set Salary = 3000 where Id = 4 Go Now execute the following query. Notice that we get the running total as expected. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees The following query uses RANGE instead of ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees Notice we don't get the running total as expected. So, the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity. All together side by side. The following query shows how running total changes 1. When no value is specified for ROWS or RANGE clause 2. When RANGE clause is used explicitly with it's default value 3. When ROWS clause is used instead of RANGE clause SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS [Default], SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range], SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows] FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range_8.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 35354 kudvenkat
How to find Oracle Database locked session using Oracle Enterprise Manager 12C
 
02:09
Video tutorial on how to find Oracle Database locked session using Oracle Enterprise Manager 12C. For more Database Administration articles and scripts go to www.aodba.com & www.verticablog.com More Articles, Scripts and How-To Papers on http://www.aodba.com
Views: 5160 AO DBA
10. Oracle Database Tutorial - Deadlock in Oracle
 
12:38
This video tutorial on Oracle on Oracle database provides detailed information about what is deadlock, how to analyze deadlock issue and how to fix dead lock issue. You can visit Oracle Database related videos here : https://www.youtube.com/watch?v=cDqlT7O8H0Q&list=PLRt-r4QiDOMfMmVU-8145pLcBxIdvAt8f&index=1 Website: http://guru4technoworld.wix.com/technoguru Facebook : https://www.facebook.com/a2zoftech/ Blog: http://dronatechnoworld.blogspot.com
Views: 56 Sandip M
Difference between where and having in sql server
 
06:36
sql server where vs having sql server group by where having group by having sql server In this vide we will discuss the difference between where and having caluses in sql server. Let us understand the difference with an example. To calculate total sales by product, we would write a GROUP BY query as shown below SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(SaleAmount) ] 1000 If we use WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product WHERE SUM(SaleAmount) ] 1000 So in short, the difference is WHERE clause cannot be used with aggregates where as HAVING can. However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed where as HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example. Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause Calculate Total sales of iPhone and Speakers using WHERE clause : In this example the WHERE clause retrieves only iPhone and Speaker products and then performs the sum. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales WHERE Product in ('iPhone', 'Speakers') GROUP BY Product Calculate Total sales of iPhone and Speakers using HAVING clause : This example retrieves all rows from Sales table, performs the sum and then removes all products except iPhone and Speakers. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING Product in ('iPhone', 'Speakers') So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY. Difference between WHERE and Having 1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups. 2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. 3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in_15.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 71258 kudvenkat
Select statement and deadlock in SQL Server
 
08:55
SELECT statement can participate in a deadlock situation in Microsoft SQL Server. It seems unnatural. Shouldn't SELECT lock nothing? This video explains how SELECT query is executed and why a deadlock may occur.
Views: 404 DBA presents
Window functions in SQL Server
 
11:00
sql server window function example window function sql server example sql server rows range clause sql server rows between 1 preceding and 1 following In this video we will discuss window functions in SQL Server In SQL Server we have different categories of window functions Aggregate functions - AVG, SUM, COUNT, MIN, MAX etc.. Ranking functions - RANK, DENSE_RANK, ROW_NUMBER etc.. Analytic functions - LEAD, LAG, FIRST_VALUE, LAST_VALUE etc... OVER Clause defines the partitioning and ordering of a rows (i.e a window) for the above functions to operate on. Hence these functions are called window functions. The OVER clause accepts the following three arguments to define a window for these functions to operate on. ORDER BY : Defines the logical order of the rows PARTITION BY : Divides the query result set into partitions. The window function is applied to each partition separately. ROWSor RANGE clause : Further limits the rows within the partition by specifying start and end points within the partition. The default for ROWS or RANGE clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Let us understand the use of ROWS or RANGE clause with an example. Compute average salary and display it against every employee We might think the following query would do the job. SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary) AS Average FROM Employees As you can see from the result, the above query does not produce the overall salary average. It produces the average of the current row and the rows preceeding the current row. This is because, the default value of ROWS or RANGE clause (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is applied. To fix this, provide an explicit value for ROWS or RANGE clause as shown below. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING tells the window function to operate on the set of rows starting from the first row in the partition to the last row in the partition. SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average FROM Employees The same result can also be achieved by using RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Well, what is the difference between ROWS and RANGE We will discuss this in a later video The following query can be used if you want to compute the average salary of 1. The current row 2. One row PRECEDING the current row and 3. One row FOLLOWING the current row SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Average FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/window-functions-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/window-functions-in-sql-server_7.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 61913 kudvenkat
SQL script to insert into many to many table
 
14:52
Text Article http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many.html Slides http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many_6.html SQL Server Interview Questions and Answers text articles & slides http://csharp-video-tutorials.blogspot.com/2014/05/sql-server-interview-questions-and.html SQL Server Interview Questions and Answers playlist https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss how to insert data into a table that has many-to-many relationship Create table Students ( Id int primary key identity, StudentName nvarchar(50) ) Go Create table Courses ( Id int primary key identity, CourseName nvarchar(50) ) Go Create table StudentCourses ( StudentId int not null foreign key references Students(Id), CourseId int not null foreign key references Courses(Id) ) Go Students - Id column is identity column Courses - Id column is identity column StudentCourses - StudentId and CourseId columns are foreign keys referencing Id column in Students and Courses tables As you can see, StudentCourses is a bridge table that has many to many relationship with Students and Courses tables. This means a given student can be enrolled into many courses and a given course can have many students enrolled. Below is the question asked in an interview for SQL Server Developer role. Write a SQL script to insert data into StudentCourses table. Here are the rules that your script should follow. 1. There will be 2 inputs for the script Student Name - The name of the student who wants to enroll into a course Course Name - The name of the course the student wants to enroll into 2. If the student is already in the Students table, then use that existing Student Id. If the student is not already in the Students table, then a row for that student must be inserted into the Students table, and use that new student id. 3. Along the same lines, if the course is already in the Courses table, then use that existing Course Id. If the course is not already in the Courses table, then a row for that course must be inserted into the Courses table, and use that new course id. 4. There should be no duplicate student course enrollments, i.e a given student must not be enrolled in the same course twice. For example, Tom must not be enrolled in C# course twice. Answer : To avoid duplicate student course enrollments create a composite primary key on StudentId and CourseId columns in StudentCourses table. With this composite primary key in place, if someone tries to enroll the same student in the same course again we get violation of primary key constraint error. Alter table StudentCourses Add Constraint PK_StudentCourses Primary Key Clustered (CourseId, StudentId) Here is the SQL script that inserts data into the 3 tables as expected Declare @StudentName nvarchar(50) = 'Sam' Declare @CourseName nvarchar(50) = 'SQL Server' Declare @StudentId int Declare @CourseId int -- If the student already exists, use the existing student ID Select @StudentId = Id from Students where StudentName = @StudentName -- If the course already exists, use the existing course ID Select @CourseId = Id from Courses where CourseName = @CourseName -- If the student does not exist in the Students table If (@StudentId is null) Begin -- Insert the student Insert into Students values(@StudentName) -- Get the Id of the student Select @StudentId = SCOPE_IDENTITY() End -- If the course does not exist in the Courses table If (@CourseId is null) Begin -- Insert the course Insert into Courses values(@CourseName) -- Get the Id of the course Select @CourseId = SCOPE_IDENTITY() End -- Insert StudentId & CourseId in StudentCourses table Insert into StudentCourses values(@StudentId, @CourseId) If required, we can very easily convert this into a stored procedure as shown below. Create procedure spInsertIntoStudentCourses @StudentName nvarchar(50), @CourseName nvarchar(50) as Begin Declare @StudentId int Declare @CourseId int Select @StudentId = Id from Students where StudentName = @StudentName Select @CourseId = Id from Courses where CourseName = @CourseName If (@StudentId is null) Begin Insert into Students values(@StudentName) Select @StudentId = SCOPE_IDENTITY() End If (@CourseId is null) Begin Insert into Courses values(@CourseName) Select @CourseId = SCOPE_IDENTITY() End Insert into StudentCourses values(@StudentId, @CourseId) End Use the following statement to execute the stored procedure Execute spInsertIntoStudentCourses 'Tom','C#'
Views: 89390 kudvenkat
03 Dead Lock in oracle database
 
11:37
DML Locks DML locks or data locks guarantee the integrity of data being accessed concurrently by multiple users. DML locks help to prevent damage caused by interference from simultaneous conflicting DML or DDL operations. By default, DML statements acquire both table-level locks and row-level locks. The reference for each type of lock or lock mode is the abbreviation used in the Locks Monitor from Oracle Enterprise Manager (OEM). For example, OEM might display TM for any table lock within Oracle rather than show an indicator for the mode of table lock (RS or SRX). Row Locks (TX) Row-level locks serve a primary function to prevent multiple transactions from modifying the same row. Whenever a transaction needs to modify a row, a row lock is acquired by Oracle. There is no hard limit on the exact number of row locks held by a statement or transaction. Also, unlike other database platforms, Oracle will never escalate a lock from the row level to a coarser granular level. This row locking ability provides the DBA with the finest granular level of locking possible and, as such, provides the best possible data concurrency and performance for transactions. The mixing of multiple concurrency levels of control and row level locking means that users face contention for data only whenever the same rows are accessed at the same time. Furthermore, readers of data will never have to wait for writers of the same data rows. Writers of data are not required to wait for readers of these same data rows except in the case of when a SELECT... FOR UPDATE is used. Writers will only wait on other writers if they try to update the same rows at the same point in time. In a few special cases, readers of data may need to wait for writers of the same data. For example, concerning certain unique issues with pending transactions in distributed database environments with Oracle. Transactions will acquire exclusive row locks for individual rows that are using modified INSERT, UPDATE, and DELETE statements and also for the SELECT with the FOR UPDATE clause. Modified rows are always locked in exclusive mode with Oracle so that other transactions do not modify the row until the transaction which holds the lock issues a commit or is rolled back. In the event that the Oracle database transaction does fail to complete successfully due to an instance failure, then Oracle database block level recovery will make a row available before the entire transaction is recovered. The Oracle database provides the mechanism by which row locks acquire automatically for the DML statements mentioned above. Whenever a transaction obtains row locks for a row, it also acquires a table lock for the corresponding table. Table locks prevent conflicts with DDL operations that would cause an override of data changes in the current transaction. Table Locks (TM) What are table locks in Oracle? Table locks perform concurrency control for simultaneous DDL operations so that a table is not dropped in the middle of a DML operation, for example. When Oracle issues a DDL or DML statement on a table, a table lock is then acquired. As a rule, table locks do not affect concurrency of DML operations. Locks can be acquired at both the table and sub-partition level with partitioned tables in Oracle. A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table, and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it. A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.
Views: 261 Md Arshad
Prevent sql injection with dynamic sql
 
10:28
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql_5.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss, how to prevent SQL injection when using dynamic SQL. In Part 140, we have implemented "Search Page" using dynamic SQL. Since we have used parameters to build our dynamic SQL statements, it is not prone to SQL Injection attack. This is an example of good dynamic SQL implementation. I have seen lot of software developers, not just the beginners but even experienced developers, buidling their dynamic sql queries by concatenating strings instead of using parameters without realizing that they are opening the doors for SQL Injection. Please check my blog at the following link for code used in the demo. http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Since we are concatenating the user input values to build the dynamic sql statement, the end user can very easily inject sql. Imagine, what happens for example, if the user enters the following in the "Firstname" textbox. ' Drop database SalesDB -- With the above SQL injected into the "Firstname" textbox, if you click the "Search" button, the following is the query which is sent to SQL server. This will drop the SalesDB. Select * from Employees where 1 = 1 AND FirstName = '' Drop database SalesDB --' On the other hand, if you use parameters to build your dynamic SQL statements, SQL Injection is not an issue. The following input in the "Firstname" textbox, would not drop the SalesDB database. ' Drop database SalesDB -- The text the user has provided in the "Firstname" textbox is treated as the value for @Firstname parameter. The following is the query that is generated and executed. exec sp_executesql N'Select * from Employees where 1 = 1 AND [email protected]',N'@FirstName nvarchar(26)',@FirstName=N''' Drop database SalesDB --' We don't have this problem of sql injection if we are using stored procedures. "SearchPageWithoutDynamicSQL.aspx" is using the stored procedure "spSearchEmployees" instead of dynamic SQL. The same input in the "Firstname" texbox on this page, would generate the following. Notice, whatever text we typed in the "Firstname" textbox is treated as the value for @FirstName parameter. exec spSearchEmployees @FirstName=N''' Drop database SalesDB --' An important point to keep in mind here is that if you have dynamic SQL in your stored procedure, and you are concatenating strings in that stored procedure to build your dynamic sql statements instead of using parameters, it is still prone to SQL injection. If this is not clear at the moment don't worry, we will discuss an example of this in out next video. So in summary, while dynamic sql provides great flexibility when implementing complicated logic with lot of permutations and combinations, if not properly implemented it may open doors for sql injection. Always use parameters to build dynamic sql statements, instead of concatenating user input values. Another benefit of using parameters to build dynamic sql statements is that it allows cached query plans to be reused, which greatly increases the performance. We will discuss an example of this in our upcoming videos.
Views: 20919 kudvenkat
IIF function in SQL Server
 
04:00
iif function in sql server 2012 iif function in sql server example sql server iif function example In this video we will discuss IIF function in SQL Server. IIF function Introduced in SQL Server 2012 Returns one of two the values, depending on whether the Boolean expression evaluates to true or false IIF is a shorthand way for writing a CASE expression Syntax : IIF ( boolean_expression, true_value, false_value ) Example : Returns Male as the boolean expression evaluates to TRUE DECLARE @Gender INT SET @Gender = 1 SELECT IIF( @Gender = 1, 'Male', 'Femlae') AS Gender Example : Using IIF() function with table data. We will use the following Employees table for this example. SQL Script to create Employees table Create table Employees ( Id int primary key identity, Name nvarchar(10), GenderId int ) Go Insert into Employees values ('Mark', 1) Insert into Employees values ('John', 1) Insert into Employees values ('Amy', 2) Insert into Employees values ('Ben', 1) Insert into Employees values ('Sara', 2) Insert into Employees values ('David', 1) Go Write a query to display Gender along with employee Name and GenderId. We can achieve this either by using CASE or IIF. Using CASE statement SELECT Name, GenderId, CASE WHEN GenderId = 1 THEN 'Male' ELSE 'Female' END AS Gender FROM Employees Using IIF function SELECT Name, GenderId, IIF(GenderId = 1, 'Male', 'Female') AS Gender FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/iif-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/iif-function-in-sql-server_14.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 34451 kudvenkat
Part 6   Transform rows into columns in sql server
 
08:53
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/06/part-6-transform-rows-into-columns-in.html This is another common sql server interview question. We will be using Countries table in this example. SQL to create the table Create Table Countries ( Country nvarchar(50), City nvarchar(50) ) GO Insert into Countries values ('USA','New York') Insert into Countries values ('USA','Houston') Insert into Countries values ('USA','Dallas') Insert into Countries values ('India','Hyderabad') Insert into Countries values ('India','Bangalore') Insert into Countries values ('India','New Delhi') Insert into Countries values ('UK','London') Insert into Countries values ('UK','Birmingham') Insert into Countries values ('UK','Manchester') Here is the interview question. Write a sql query to transpose rows to columns. Using PIVOT operator we can very easily transform rows to columns. Select Country, City1, City2, City3 From ( Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence from Countries ) Temp pivot ( max(City) for ColumnSequence in (City1, City2, City3) ) Piv
Views: 199007 kudvenkat
Pick a Card Tarot💰WHAT'S BLOCKING MY FLOW?!?💕
 
01:02:17
TIMESTAMPS: Select your Reading 3:42 DRAW ONE 4:09 DRAW TWO 25:10 DRAW THREE 42:10 This reading helps you to answer what is blocking your ability to make progress in any area of your life: LOVE, WORK, MONEY, KARMA etc. Don't forget to check out OCTOBER LOVE READINGS FOR ALL SIGNS, uploaded yesterday. I have also added a video all about NUMBER SEVEN and understanding how these energies may affect you in October. October LOVE Tarot Readings: It’s been a while...! I have finally recovered from what can only be described as a very INTENSE EXPERIENCE working with the energies of September and the hangover of Saturn turning Direct in its comfortable Capricorn home. Even though I AM a Capi, let me tell you this experience was anything but comfortable for me! How did you find the time after the Virgo Full Moon? I got a very strong sense at the end of September that the energies of The Chariot (and all the 7s) would be resonant for the month of October. It just seemed to me a natural shift, in a Karmic sense, after the JUDGMENT and REVELATION of September. October is a month to re- find our BALANCE or a new EQUILIBRIUM, which also coincides with the Libra New Moon on the 8th and the fact that it is Libra’s birthday season- HAPPY BIRTHDAY LIBRA!! The NUMBER SEVEN is about UNDERSTANDING THE MYSTERIES of your life, the balance between what you think and how you feel- are these things in sync? Number Sevens CHALLENGE us to recognise our true foundations, attitudes and perspectives- do your actions match your feelings? Hopefully, these examples and questions can help you to understand some of what may have been challenging you over the last few weeks. We have also had Venus turning Retrograde on the 6th October, encouraging us to RE-ASSESS OUR RELATIONSHIPS, past and present and LET GO of anyone who is holding us back and RESTRICTING our sense of Personal Freedom or AUTHENTICITY: do you feel you can be yourself around them? Are they HONEST and truthful with you? How does this friendship/ relationship make you feel about yourself? September should have brought you some PERSONAL REVELATIONS and so far October is encouraging us to modify our approach and perspective as a result of what you learned. There are CHANGES ahead! We are beginning NEW KARMIC CYCLES at this time, a process that will continue for the next TWELVE MONTHS, so be MINDFUL of the challenges you have recently experienced and try to recognize the STRENGTH you have gained from them. Much love and many blessings and THANK YOU for being the BEST VIEWERS a girl could wish for. Next week I’ll be posting a NEW PICK A CARD reading and GENERAL BI- WEEKLIES for all signs. Many blessings. ***** Reiki and Tarot are all about compassionate self-healing and at Ki Wisdom Tarot, I give you the clarity and the information you need to become fully mindful of and resonate with your challenges and experiences. You will find free weekly, bi weekly and monthly Tarot readings for all signs on my YouTube channel, Ki Wisdom Tarot. You can also book a personal reading with me through the channel, my Facebook page or email me directly at [email protected] DISCLAIMER- For entertainment only
Views: 1297 Ki Wisdom Tarot
Dynamic SQL in SQL Server
 
12:12
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server_27.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss 1. What is Dynamic SQL 2. Simple example of using Dynamic SQL What is Dynamic SQL Dynamic SQL is a SQL built from strings at runtime. Simple example of using Dynamic SQL Here is the SQL Script to create Employees table and populate it with data Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) Go Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) Go One way to achieve this is by implementing a stored procedure as shown below that this page would call. Create Procedure spSearchEmployees @FirstName nvarchar(100), @LastName nvarchar(100), @Gender nvarchar(50), @Salary int As Begin Select * from Employees where (FirstName = @FirstName OR @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND (Gender = @Gender OR @Gender IS NULL) AND (Salary = @Salary OR @Salary IS NULL) End Go The stored procedure in this case is not very complicated as we have only 4 search filters. What if there are 20 or more such filters. This stored procedure can get complex. To make things worse what if we want to specify conditions like AND, OR etc between these search filters. The stored procedure can get extremely large, complicated and difficult to maintain. One way to reduce the complexity is by using dynamic SQL as show below. Depending on for which search filters the user has provided the values on the "Search Page", we build the WHERE clause dynamically at runtime, which can reduce complexity. However, you might hear arguments that dynamic sql is bad both in-terms of security and performance. This is true if the dynamic sql is not properly implemented. From a security standpoint, it may open doors for SQL injection attack and from a performance standpoint, the cached query plans may not be reused. If properly implemented, we will not have these problems with dynamic sql. In our upcoming videos, we will discuss good and bad dynamic sql implementations. For now let's implement a simple example that makes use of dynamic sql. In the example below we are assuming the user has supplied values only for FirstName and LastName search fields. To execute the dynamicl sql we are using system stored procedure sp_executesql. sp_executesql takes two pre-defined parameters and any number of user-defined parameters. @statement - The is the first parameter which is mandatory, and contains the SQL statements to execute @params - This is the second parameter and is optional. This is used to declare parameters specified in @statement The rest of the parameters are the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure Declare @sql nvarchar(1000) Declare @params nvarchar(1000) Set @sql = 'Select * from Employees where [email protected] and [email protected]' Set @params = '@FirstName nvarchar(100), @LastName nvarchar(100)' Execute sp_executesql @sql, @params, @FirstName='Ben',@LastName='Hoskins' This is just the introduction to dynamic SQL. If a few things are unclear at the moment, don't worry. In our upcoming videos we will discuss the following 1. Implementing a real world "Search Web Page" with and without dynamic SQL 2. Performance and Security implications of dynamic sql. Along the way we will also discuss good and bad dynamic sql implementations. 3. Different options available for executing dynamic sql and their implications 4. Using dynamic sql in stored procedures and it's implications Once we discuss all the above, you will understand 1. The flexibility dynamic sql provides 2. Advantages and disadvantages of dynamic sql 3. When and when not to use dynamic sql
Views: 43384 kudvenkat
GROUPING ID function in SQL Server
 
12:38
grouping_id function in sql server 2008 grouping_id in sql server 2008 example sql server grouping level In this video we will discuss 1. GROUPING_ID function in SQL Server 2. Difference between GROUPING and GROUPING_ID functions 3. Use of GROUPING_ID function GROUPING_ID function computes the level of grouping. Difference between GROUPING and GROUPING_ID Syntax : GROUPING function is used on single column, where as the column list for GROUPING_ID function must match with GROUP BY column list. GROUPING(Col1) GROUPING_ID(Col1, Col2, Col3,...) GROUPING indicates whether the column in a GROUP BY list is aggregated or not. Grouping returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING_ID() function concatenates all the GOUPING() functions, perform the binary to decimal conversion, and returns the equivalent integer. In short GROUPING_ID (A, B, C) = GROUPING(A) + GROUPING(B) + GROUPING(C) Let us understand this with an example. SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales, CAST(GROUPING(Continent) AS NVARCHAR(1)) + CAST(GROUPING(Country) AS NVARCHAR(1)) + CAST(GROUPING(City) AS NVARCHAR(1)) AS Groupings, GROUPING_ID(Continent, Country, City) AS GPID FROM Sales GROUP BY ROLLUP(Continent, Country, City) Row Number 1 : Since the data is not aggregated by any column GROUPING(Continent), GROUPING(Country) and GROUPING(City) return 0 and as result we get a binar string with all ZEROS (000). When this converted to decimal we get 0 which is displayed in GPID column. Row Number 7 : The data is aggregated for Country and City columns, so GROUPING(Country) and GROUPING(City) return 1 where as GROUPING(Continent) return 0. As result we get a binar string (011). When this converted to decimal we get 10 which is displayed in GPID column. Row Number 15 : This is the Grand total row. Notice in this row the data is aggregated by all the 3 columns. Hence all the 3 GROUPING functions return 1. So we get a binary string with all ONES (111). When this converted to decimal we get 7 which is displayed in GPID column. Use of GROUPING_ID function : GROUPING_ID function is very handy if you want to sort and filter by level of grouping. Sorting by level of grouping : SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales, GROUPING_ID(Continent, Country, City) AS GPID FROM Sales GROUP BY ROLLUP(Continent, Country, City) ORDER BY GPID Filter by level of grouping : The following query retrieves only continent level aggregated data SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales, GROUPING_ID(Continent, Country, City) AS GPID FROM Sales GROUP BY ROLLUP(Continent, Country, City) HAVING GROUPING_ID(Continent, Country, City) = 3 Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/groupingid-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/groupingid-function-in-sql-server_27.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 32304 kudvenkat
How To Kill All Database Processes On SQL Server
 
06:42
SQl management studio provides lot of facilities to manage SQL processes. The “Activity monitor screen” which will show you some very useful and important information about SQL processes. It will show you all active processes, there status, number of open transactions for each process, the application by which the process is generated, Wait time CPU and memory utilisation, and the host name, means the host or the machine by which the process is initiated etc. If a process is hanging, it will show the process icon with an hour glass. Refresh it several times, sometimes the icon will turn to sleep mode if the process completes its execution. If the icon remains an hour glass, the process may be hanging. Use other info as well to decide if the process is really hanging. If you double click on the process ID it will show the SQL stamen subjected to the execution of the process. If a process is hanging you can identify which application has originated the process, which SQL stamen is hanging and even more from which machine or the host the process is initiated If some application users are complaining about uneven slowness, uneven timeouts etcetera, the reason behind may be one or more processors are hanging. What can you do now? You can restart the SQL server or the service which will disconnect all databases and all applications disturbing all the users. Or otherwise you can take the database to off line and bring it back to online. Or you can disconnect all connections to the database. The disturbance may be lesser than the previous case but still lot of applications and users can be disturbed, especially in an industrial setup. And still you do not know the real reason for the slowness or the malfunctioning as well. So what can you do? Go and examine the processors. Identify exactly which process is hanging. And most probably you will be identifying how and why. So you can find permanent solution. But the immediate solution is you can kill the troubling process or the few. It will solve the issue with minimum disturbance immediately. After identifying the correct process, you can right click on the process ID and click on ‘Kill process’ button. It will kill the selected process. You can kill processors using SQL statements and, using SQL commands you can kill all processors at once as well. Code for Killing Processes Individually USE master go DECLARE @dbname sysname SET @dbname = 'YourDB' EXECUTE ('KILL 53') Code for killing all Processes USE master go DECLARE @dbname sysname SET @dbname = 'YourDB' DECLARE @spid int SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) WHILE @spid IS NOT NULL BEGIN EXECUTE ('KILL ' + @spid) SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid @spid END
Views: 6918 Rohan Mehtha
Audit table changes in sql server
 
06:45
sql server 2008 ddl trigger audit sql server table changes tracking In this video we will discuss, how to audit table changes in SQL Server using a DDL trigger. Table to store the audit data Create table TableChanges ( DatabaseName nvarchar(250), TableName nvarchar(250), EventType nvarchar(250), LoginName nvarchar(250), SQLCommand nvarchar(2500), AuditDateTime datetime ) Go The following trigger audits all table changes in all databases on a SQL Server. CREATE TRIGGER tr_AuditTableChanges ON ALL SERVER FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS BEGIN DECLARE @EventData XML SELECT @EventData = EVENTDATA() INSERT INTO SampleDB.dbo.TableChanges (DatabaseName, TableName, EventType, LoginName, SQLCommand, AuditDateTime) VALUES ( @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'), @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'), GetDate() ) END In the above example we are using EventData() function which returns event data in XML format. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server_12.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 42408 kudvenkat
One SQL Cheat Code For Amazingly Fast JSON Queries - SQL JSON Index performance
 
10:41
How non-persisted computed column indexes make your JSON queries high performance. Did you know that non-persisted columns actually get saved to the index pages? This makes JSON querying incredibly fast in SQL Server. Please like and subscribe if you enjoyed this video! Blog post: https://bertwagner.com/2017/05/09/one-sql-cheat-code-for-amazingly-fast-json-queries/ Want to receive the latest weekly blog posts and videos in your inbox? Sign up for the newsletter here: https://upscri.be/c77fc8/ Elsewhere on the internet: https://bertwagner.com https://twitter.com/bertwagner
Views: 3010 Bert Wagner
Oracle Database Tutorial 70: IO ERROR: The network adapter could not establish the connection
 
03:34
IO ERROR: The network adapter could not establish the connection Learn how to solve this Oracle Database / SQL Developer Error with in 4 minutes ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/sql-developer-error-the-network-adapter-could-not-establish-the-connection Previous Tutorial ► Intersect and Minus SQL set operators https://youtu.be/sDZFpwEw1k8 ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►►►Help Me In Getting A Job◄◄◄ ►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company Thanks ------------------------------------------------------------------------- Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=kb4rc1 -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! -------------------------------------------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 157444 Manish Sharma
SQL Server deadlock error handling
 
05:00
In this video we will discuss how to catch deadlock error using try/catch in SQL Server. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling_30.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists Modify the stored procedure as shown below to catch the deadlock error. The code is commented and is self-explanatory. Alter procedure spTransaction1 as Begin Begin Tran Begin Try Update TableA Set Name = 'Mark Transaction 1' where Id = 1 Waitfor delay '00:00:05' Update TableB Set Name = 'Mary Transaction 1' where Id = 1 -- If both the update statements succeeded. -- No Deadlock occurred. So commit the transaction. Commit Transaction Select 'Transaction Successful' End Try Begin Catch -- Check if the error is deadlock error If(ERROR_NUMBER() = 1205) Begin Select 'Deadlock. Transaction failed. Please retry' End -- Rollback the transaction Rollback End Catch End Alter procedure spTransaction2 as Begin Begin Tran Begin Try Update TableB Set Name = 'Mary Transaction 2' where Id = 1 Waitfor delay '00:00:05' Update TableA Set Name = 'Mark Transaction 2' where Id = 1 Commit Transaction Select 'Transaction Successful' End Try Begin Catch If(ERROR_NUMBER() = 1205) Begin Select 'Deadlock. Transaction failed. Please retry' End Rollback End Catch End After modifying the stored procedures, execute both the procedures from 2 different windows simultaneously. Notice that the deadlock error is handled by the catch block. In our next video, we will discuss how applications using ADO.NET can handle deadlock errors. Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling.html
Views: 32577 kudvenkat
Grouping function in SQL Server
 
12:49
sql server rollup grouping sql server rollup replace null sql server rollup grouping replace null In this video we will discuss the use of Grouping function in SQL Server. This is continuation to Part 104. Please watch Part 104 from SQL Server tutorial before proceeding. What is Grouping function Grouping(Column) indicates whether the column in a GROUP BY list is aggregated or not. Grouping returns 1 for aggregated or 0 for not aggregated in the result set. The following query returns 1 for aggregated or 0 for not aggregated in the result set SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales, GROUPING(Continent) AS GP_Continent, GROUPING(Country) AS GP_Country, GROUPING(City) AS GP_City FROM Sales GROUP BY ROLLUP(Continent, Country, City) What is the use of Grouping function in real world When a column is aggregated in the result set, the column will have a NULL value. If you want to replace NULL with All then this GROUPING function is very handy. SELECT CASE WHEN GROUPING(Continent) = 1 THEN 'All' ELSE ISNULL(Continent, 'Unknown') END AS Continent, CASE WHEN GROUPING(Country) = 1 THEN 'All' ELSE ISNULL(Country, 'Unknown') END AS Country, CASE WHEN GROUPING(City) = 1 THEN 'All' ELSE ISNULL(City, 'Unknown') END AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Can't I use ISNULL function instead as shown below SELECT ISNULL(Continent, 'All') AS Continent, ISNULL(Country, 'All') AS Country, ISNULL(City, 'All') AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Well, you can, but only if your data does not contain NULL values. Let me explain what I mean. At the moment the raw data in our Sales has no NULL values. Let's introduce a NULL value in the City column of the row where Id = 1 Update Sales Set City = NULL where Id = 1 Now execute the following query with ISNULL function SELECT ISNULL(Continent, 'All') AS Continent, ISNULL(Country, 'All') AS Country, ISNULL(City, 'All') AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Notice that the actuall NULL value in the raw data is also replaced with the word 'All', which is incorrect. Hence the need for Grouping function. Please note : Grouping function can be used with Rollup, Cube and Grouping Sets Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/grouping-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/grouping-function-in-sql-server_25.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 39294 kudvenkat
How to drop an object, and then recover it from the Oracle recycle bin
 
02:53
How to drop an object, and then recover it from the Oracle recycle bin ========================================================== select base_object from recyclebin; flashback table tablename to before drop; create table ============= create table fish as select * from tab; insert data into table ====================== insert into fish select * from tab; insert into fish select * from fish; drop table =========== drop table fish; how to recover it from recyclebin ================================== flashback table fish to before drop;
Views: 260 Praveen Biyyapu
Cross apply and outer apply in sql server
 
08:16
cross apply sql server example sql server outer apply example sql server join table function with parameter sql server inner join table valued function In this video we will discuss cross apply and outer apply in sql server with examples. We want to retrieve all the matching rows between Department and Employee tables. This can be very easily achieved using an Inner Join as shown below. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Inner Join Employee E On D.Id = E.DepartmentId Now if we want to retrieve all the matching rows between Department and Employee tables + the non-matching rows from the LEFT table (Department) This can be very easily achieved using a Left Join as shown below. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Left Join Employee E On D.Id = E.DepartmentId Now let's assume we do not have access to the Employee table. Instead we have access to the following Table Valued function, that returns all employees belonging to a department by Department Id. Create function fn_GetEmployeesByDepartmentId(@DepartmentId int) Returns Table as Return ( Select Id, Name, Gender, Salary, DepartmentId from Employee where DepartmentId = @DepartmentId ) Go The following query returns the employees of the department with Id =1. Select * from fn_GetEmployeesByDepartmentId(1) Now if you try to perform an Inner or Left join between Department table and fn_GetEmployeesByDepartmentId() function you will get an error. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Inner Join fn_GetEmployeesByDepartmentId(D.Id) E On D.Id = E.DepartmentId If you execute the above query you will get the following error Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "D.Id" could not be bound. This is where we use Cross Apply and Outer Apply operators. Cross Apply is semantically equivalent to Inner Join and Outer Apply is semantically equivalent to Left Outer Join. Just like Inner Join, Cross Apply retrieves only the matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Cross Apply fn_GetEmployeesByDepartmentId(D.Id) E Just like Left Outer Join, Outer Apply retrieves all matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function + non-matching rows from the left table (Department) Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Outer Apply fn_GetEmployeesByDepartmentId(D.Id) E How does Cross Apply and Outer Apply work The APPLY operator introduced in SQL Server 2005, is used to join a table to a table-valued function. The Table Valued Function on the right hand side of the APPLY operator gets called for each row from the left (also called outer table) table. Cross Apply returns only matching rows (semantically equivalent to Inner Join) Outer Apply returns matching + non-matching rows (semantically equivalent to Left Outer Join). The unmatched columns of the table valued function will be set to NULL. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql_8.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 78162 kudvenkat
How to use partitioning to improve performance of large tables
 
42:49
In this video we cover in much more detail the improvement in performance that can be achieved by using partitioning. MS SQL server databases can scale well using this feature. We cover how partitioning a table gives similar performance as a single table with a clustered index, we then explore how adding NC index improve performance of the heap table as well as the partitioned table.
Views: 19401 Jayanth Kurup
This Site Can't Be Reached ERR_CONNECTION_REFUSED in Google chrome- Fixed easily
 
11:06
Most of Google chrome users must have faced the issue ‘This site can’t be reached error‘. Don’t worry devicemanager is at your disposal to fix this issue easily. The cause of this error is that DNS lookup failed so the webpage is not available. In this video I have given five methods to solve the problem. Watch the video fully. ---------------------------------------------------------------------------------------------- Method 1: Restart DNS client Method 2: Change IPv4 DNS Address.Type the following address in the Preferred DNS server and Alternate DNS server: 8.8.8.8 8.8.4.4 Method 3: Try resetting TCP/IP (a) ipconfig /release (b) ipconfig /all (c) ipconfig /flushdns (d) ipconfig /renew (e) netsh int ip set dns (f) netsh winsock reset Method 4: Reset Gooogle Chrome settings Method 5: Reinstall Chrome ----------------------------------------------------------------------------------------------- The fix shown in the video works even for the issues like Fix This site can't be reached|ERR_CONNECTION_REFUSED in Google chrome-I got the error message while surfing the internet. This site can’t be reached-this site cannot be reached this website refused to connect. Try: Reload the page Check the connection Check the proxy and the firewall ERR_CONNECTION_REFUSED. Check your Internet connection Check any cables and reboot any routers, modems or other network devices you may be using. Allow Chrome to access the network in your firewall or antivirus settings. If it is already listed as a program allowed to access the network, try removing it from the list and adding it again. If you use a proxy server. Check your proxy settings or contact your network administrator to make sure that the proxy server is working. If you don't believe you should be using a proxy server: Go to the Chrome menu - Settings - - Show advanced settings - Change proxy settings... - LAN Settings and deselect "Use a proxy server for your LAN". Then try to troubleshoot the error by using windows troubleshooter,finally it shows the following error. "The remote device or resource won't accept the connection"-ERR_CONNECTION_REFUSED,this method is working in windows 7,windows 8/windows 8.1/windows 10 too. Solution:1 Go to settings-show advanced settings-network-change proxy settings select connections tab-Lan settings tick automatically detect settings option. Solution:2 go to network and sharing center then select your network adapter and then click properties. select internet protocol version 4 then tick automatically detect settings option. finally the error is fixed. --------------------------------------------------------------------------------------------------- Check this Url : https://youtu.be/HznL6MXcGJ8 for the following issues. 1.how to fix this site cannot be reached google chrome 2.site cannot be reached chrome 3.site cannot be reached fix 4.site cannot be reached windows 10 5.site cannot be reached problem 6.site cannot be reached fix windows 10 7.This site can’t be reached [FIX] [Google Chrome] 8.FIX "THIS SITE CAN'T BE REACHED" - CHROME - (2016) 9.ERR CONNECTION REFUSED 10.this site cannot be reached google chrome 11.this site can’t be reached 12.this site cannot be reached 13.the site cannot be reached 14.ERR CONNECTION RESET 15.chrome error connection reset 16.chrome this site can't be reached 17.can't be reached 18.ERR CONNECTION TIMED OUT --------------------------------------------------------------------------- Please share the video https://youtu.be/HznL6MXcGJ8
Views: 836602 DEVICE MANAGER
Table valued parameters in SQL Server
 
06:46
table valued parameters example send table variable to stored procedure pass table variable as parameter to stored procedure pass table variable to sql stored procedure In this video we will discuss table valued parameters in SQL Server. Table Valued Parameter is a new feature introduced in SQL SERVER 2008. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. Prior to SQL SERVER 2008, it is not possible to pass a table variable as a parameter to a stored procedure. Let us understand how to pass multiple rows to a stored procedure using Table Valued Parameter with an example. We want to insert multiple rows into the following Employees table. SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10) ) Go Step 1 : Create User-defined Table Type CREATE TYPE EmpTableType AS TABLE ( Id INT PRIMARY KEY, Name NVARCHAR(50), Gender NVARCHAR(10) ) Go Step 2 : Use the User-defined Table Type as a parameter in the stored procedure. Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc. CREATE PROCEDURE spInsertEmployees @EmpTableType EmpTableType READONLY AS BEGIN INSERT INTO Employees SELECT * FROM @EmpTableType END Step 3 : Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure. DECLARE @EmployeeTableType EmpTableType INSERT INTO @EmployeeTableType VALUES (1, 'Mark', 'Male') INSERT INTO @EmployeeTableType VALUES (2, 'Mary', 'Female') INSERT INTO @EmployeeTableType VALUES (3, 'John', 'Male') INSERT INTO @EmployeeTableType VALUES (4, 'Sara', 'Female') INSERT INTO @EmployeeTableType VALUES (5, 'Rob', 'Male') EXECUTE spInsertEmployees @EmployeeTableType That's it. Now select the data from Employees table and notice that all the rows of the table variable are inserted into the Employees table. In our next video, we will discuss how to pass table as a parameter to the stored procedure from an ADO.NET application Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/table-valued-parameters-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/table-valued-parameters-in-sql-server_17.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 66407 kudvenkat
Managing Resumable Space Allocation in Oracle database
 
02:56
By Vivek Saxena Insa Shabad Coachings Shamli Resumable Space Allocation is given by extending the data file size with a open database Alter session enable resumable; Create tablespace spr datafile 'c:\db3\data\spr1a.dbf' size 100k; Create table spr(rn number(10) tablespace spr; Insert into spr(select salary from hr.employees); Insert into spr (select * from spr); Set oracle_sid= orcl Sqlplus/nolog Conn/as syadba Alter database datafile 'c:\db3\data\spr1a.dbf' resize 300k;
Understanding RID Lock Part 1 in sql server
 
05:01
RID LOcking Demo : CReate table Demo_lock(Id int,name varchar(1000)) select * from Demo_lock go insert into Demo_lock select 1,'Shrikant' go begin transaction insert into Demo_lock select 2,'Demo'
Views: 637 SqlIsEasy
OBIEE Dynamic Session Variables - Creating and Using in an Analysis - OBIEE Online Training
 
12:33
This video tutorial demonstrates how to create an OBIEE database-driven session variable using the BI Admin Tool. The initialization block selects data from a database table to set the value of the variable. After creating the session variable and init block, we can then use the variable in an analysis filter, and well as display the value in the title view.- https://www.fireboxtraining.com/obiee-training
Views: 31006 Firebox Training
How To Fix Java Your Security Settings Have Blocked A Self Signed Application From Running
 
03:28
http://pcroger.com/fix-java-security-settings-blocked-self-signed-application-running/ Learn how to fix the Java error message "Your Security Settings Have Blocked A Self-Signed Application From Running"; most prevalent after updating to Java 7 Update 51. Contrary to what you might be told this is not a problem with your computer, just a security issue. It's easy to fix, watch this short video to find out how. If you have a Java application that won't run this might be the fix you need. Head over to my website at the link above for additional screen shots and a link to the Java page explaining the issue further.
Views: 461538 PC Roger
Why do We Have Repeatable Read and Serializable Isolation Levels?
 
10:00
A free lesson from the course, "Repeatable Read and Serializable Isolation Levels" from SQLWorkbooks.com: https://sqlworkbooks.com/course/repeatable-read-and-serializable-isolation-levels-45-minutes/
Query Tuning 101 How to Run Autotrace in SQL Developer
 
02:21
This video shows how to run autotrace reports using Oracle SQL Developer to analyze query performance. It also discusses the privileges you need to enable database users to run autotrace. ============================ The Magic of SQL with Chris Saxon Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 19516 The Magic of SQL
Lead and Lag functions in SQL Server 2012
 
07:27
In this video we will discuss about Lead and Lag functions. Lead and Lag functions Introduced in SQL Server 2012 Lead function is used to access subsequent row data along with current row data Lag function is used to access previous row data along with current row data ORDER BY clause is required PARTITION BY clause is optional Syntax LEAD(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...) LAG(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...) Offset - Number of rows to lead or lag. Default_Value - The default value to return if the number of rows to lead or lag goes beyond first row or last row in a table or partition. If default value is not specified NULL is returned. We will use the following Employees table for the examples in this video SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 1000) Insert Into Employees Values (2, 'John', 'Male', 2000) Insert Into Employees Values (3, 'Pam', 'Female', 3000) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 5000) Insert Into Employees Values (6, 'Mary', 'Female', 6000) Insert Into Employees Values (7, 'Ben', 'Male', 7000) Insert Into Employees Values (8, 'Jodi', 'Female', 8000) Insert Into Employees Values (9, 'Tom', 'Male', 9000) Insert Into Employees Values (10, 'Ron', 'Male', 9500) Go Lead and Lag functions example WITHOUT partitions : This example Leads 2 rows and Lags 1 row from the current row. When you are on the first row, LEAD(Salary, 2, -1) allows you to move forward 2 rows and retrieve the salary from the 3rd row. When you are on the first row, LAG(Salary, 1, -1) allows us to move backward 1 row. Since there no rows beyond row 1, Lag function in this case returns the default value -1. When you are on the last row, LEAD(Salary, 2, -1) allows you to move forward 2 rows. Since there no rows beyond the last row 1, Lead function in this case returns the default value -1. When you are on the last row, LAG(Salary, 1, -1) allows us to move backward 1 row and retrieve the salary from the previous row. SELECT Name, Gender, Salary, LEAD(Salary, 2, -1) OVER (ORDER BY Salary) AS Lead_2, LAG(Salary, 1, -1) OVER (ORDER BY Salary) AS Lag_1 FROM Employees Lead and Lag functions example WITH partitions : Notice that in this example, Lead and Lag functions return default value if the number of rows to lead or lag goes beyond first row or last row in the partition. SELECT Name, Gender, Salary, LEAD(Salary, 2, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lead_2, LAG(Salary, 1, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lag_1 FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server_5.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 52172 kudvenkat
Oracle X$TRACE, Wait Event Internals and Background Process Communication
 
01:48:41
In this session we will look into some internals of Oracle background process communication and also some special types of wait events that most people aren’t aware of. We will use some exotic tracing for internals research and fun and some of this stuff is actually useful in real life too! I’m not going to reveal everything upfront, as this is a secret internals hacking session after all ;-) We will use various techniques to research what the “reliable message” wait event is about and how reliable background process communication is orchestrated in Oracle. This is a hacking session, not formal structured training, so I’ll just do free form demos and talk (probably no slides, just hacking stuff on the command line). Also, check out my full-week Advanced Oracle Troubleshooting online training at https://blog.tanelpoder.com/seminar
Views: 3348 Tanel Poder
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
 
01:49
solution of error: Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Views: 87624 INNo MATHs
Sequence object in SQL Server 2012
 
12:22
create sequence in sql server In this video we will discuss sequence object in SQL Server. Sequence object Introduced in SQL Server 2012 Generates sequence of numeric values in an ascending or descending order Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/sequence-object-in-sql-server-2012.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/sequence-object-in-sql-server-2012_26.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists Creating the Incrementing Sequence : The following code create a Sequence object that starts with 1 and increments by 1 CREATE SEQUENCE [dbo].[SequenceObject] AS INT START WITH 1 INCREMENT BY 1 Generating the Next Sequence Value : Now we have a sequence object created. To generate the sequence value use NEXT VALUE FOR clause SELECT NEXT VALUE FOR [dbo].[SequenceObject] Output : 1 Every time you execute the above query the sequence value will be incremented by 1. I executed the above query 5 times, so the current sequence value is 5. Retrieving the current sequence value : If you want to see what the current Sequence value before generating the next, use sys.sequences SELECT * FROM sys.sequences WHERE name = 'SequenceObject' Alter the Sequence object to reset the sequence value : ALTER SEQUENCE [SequenceObject] RESTART WITH 1 Select the next sequence value to make sure the value starts from 1 SELECT NEXT VALUE FOR [dbo].[SequenceObject] Using sequence value in an INSERT query : CREATE TABLE Employees ( Id INT PRIMARY KEY, Name NVARCHAR(50), Gender NVARCHAR(10) ) -- Generate and insert Sequence values INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Ben', 'Male') INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Sara', 'Female') -- Select the data from the table SELECT * FROM Employees Creating the decrementing Sequence : The following code create a Sequence object that starts with 100 and decrements by 1 CREATE SEQUENCE [dbo].[SequenceObject] AS INT START WITH 100 INCREMENT BY -1 Specifying MIN and MAX values for the sequence : Use the MINVALUE and MAXVALUE arguments to specify the MIN and MAX values respectively. Step 1 : Create the Sequence object CREATE SEQUENCE [dbo].[SequenceObject] START WITH 100 INCREMENT BY 10 MINVALUE 100 MAXVALUE 150 Step 2 : Retrieve the next sequence value. The sequence value starts at 100. Every time we call NEXT VALUE, the value will be incremented by 10. SELECT NEXT VALUE FOR [dbo].[SequenceObject] If you call NEXT VALUE, when the value reaches 150 (MAXVALUE), you will get the following error The sequence object 'SequenceObject' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Recycling Sequence values : When the sequence object has reached it's maximum value, and if you want to restart from the minimum value, set CYCLE option ALTER SEQUENCE [dbo].[SequenceObject] INCREMENT BY 10 MINVALUE 100 MAXVALUE 150 CYCLE At this point, whe the sequence object has reached it's maximum value, and if you ask for the NEXT VALUE, sequence object starts from the minimum value again which in this case is 100. To improve performance, the Sequence object values can be cached using the CACHE option. When the values are cached they are read from the memory instead of from the disk, which improves the performance. When the cache option is specified you can also specify the size of th cache , that is the number of values to cache. The following example, creates the sequence object with 10 values cached. When the 11th value is requested, the next 10 values will be cached again. CREATE SEQUENCE [dbo].[SequenceObject] START WITH 1 INCREMENT BY 1 CACHE 10 Using SQL Server Graphical User Interface (GUI) to create the sequence object : 1. Expand the database folder 2. Expand Programmability folder 3. Right click on Sequences folder 4. Select New Sequence Next video : Difference between SEQUENCE and IDENTITY in SQL Server
Views: 36433 kudvenkat
Rollup in SQL Server
 
09:01
sql server group by rollup sql server 2008 group by rollup group by with rollup example sql rollup example sql server rollup example sql server 2008 rollup examples rollup clause in sql server rollup function in sql server ROLLUP in SQL Server is used to do aggregate operation on multiple levels in hierarchy. Let us understand Rollup in SQL Server with examples. We will use the following Employees table for the examples in this video. Retrieve Salary by country along with grand total There are several ways to achieve this. The easiest way is by using Rollup with GroupBy. SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY ROLLUP(Country) The above query can also be rewritten as shown below SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country WITH ROLLUP We can also use UNION ALL operator along with GROUP BY SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country UNION ALL SELECT NULL, SUM(Salary) AS TotalSalary FROM Employees We can also use Grouping Sets to achieve the same result SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY GROUPING SETS ( (Country), () ) Let's look at another example. Group Salary by Country and Gender. Also compute the Subtotal for Country level and Grand Total as shown below. Using ROLLUP with GROUP BY SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY ROLLUP(Country, Gender) OR SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country, Gender WITH ROLLUP Using UNION ALL with GROUP BY SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country, Gender UNION ALL SELECT Country, NULL, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country UNION ALL SELECT NULL, NULL, SUM(Salary) AS TotalSalary FROM Employees Using GROUPING SETS SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY GROUPING SETS ( (Country, Gender), (Country), () ) Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server_20.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 57311 kudvenkat
FIRST VALUE function in SQL Server
 
02:48
In this video we will discuss FIRST_VALUE function in SQL Server FIRST_VALUE function Introduced in SQL Server 2012 Retrieves the first value from the specified column ORDER BY clause is required PARTITION BY clause is optional Syntax : FIRST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...) FIRST_VALUE function example WITHOUT partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the entire table. SELECT Name, Gender, Salary, FIRST_VALUE(Name) OVER (ORDER BY Salary) AS FirstValue FROM Employees FIRST_VALUE function example WITH partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the respective partition. SELECT Name, Gender, Salary, FIRST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary) AS FirstValue FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/firstvalue-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/firstvalue-function-in-sql-server_6.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 34817 kudvenkat
NINJA TEACHES YOU NEW BUILDING AND TRAP TECHNIQUES - Fortnite Funny & WTF Moments #68
 
10:03
*NEW* OP TECHNIQUES/GLITCHES: Roof Block, Double Traps, Pyramid Base | NINJA First time being A BUSH on Sky Base #68 ► 5,000 V-bucks Giveaway weekly: Like the video, subscribe to the channel and comment "I want v-bucks" to get an entry to the giveaway. 5,000 v-bucks will be given to two random winners every week (Sunday) ► Subscribe to Fortnite LOL channel: https://www.youtube.com/channel/UCtqLiye3D3fPBq7ZyiwsHdQ ► Is this a fake giveaway? No, I will be select randomly among the comments and send the v-bucks by paypal or gift card. ► When will the winner be announced? Sunday every week. ► How do I know if I win? Follow my twitter at https://twitter.com/ClashLOL_YT to know the result. ---- What's up guys, welcome to the #68 of our Fortnite Fails & Funny Moments series! In todays we have put together some of the funniest Fortnite moments including wtf moments to funny fails! Can we smash 5,000 likes for episode #69 tomorrow!? ► Send your moments here: https://goo.gl/forms/VwXT348DNj1XRBo63 Or just Shoot me an email: [email protected] ------ Thanks to everyone who was a part of todays video. You can find these awesome people below for more amazing Fortnite content: ► Note: If there are any mistakes with the credits, or someone submitted your clip claiming it was theirs please email us: [email protected] Thank you! https://www.twitch.tv/Cizzorz https://www.twitch.tv/CouRageJD https://www.twitch.tv/dakotaz https://www.twitch.tv/Loserfruit https://www.twitch.tv/MitchJones https://www.twitch.tv/NEACE https://www.twitch.tv/Ninja https://www.twitch.tv/Tfue https://www.twitch.tv/ThatHalalGamer https://www.twitch.tv/TSM_Daequan https://www.twitch.tv/TSM_Hamlinz https://www.twitch.tv/Ursidae https://www.twitch.tv/zvmi https://www.twitch.tv/嬌兔 BL4CK DiAMOND Gaming https://www.youtube.com/channel/UCqb9ZeWpW6CnphrGix3z0sA https://www.reddit.com/user/ DEUTSCHLANDDD https://www.twitch.tv/NW_PoiSoN https://www.reddit.com/user/NW_PoiSoN https://www.youtube.com/c/Thuned https://www.youtube.com/thedoctorfootball https://www.twitch.tv/DrLupo ►Recent videos: Ninja Trolls A No Skin | 1,000 IQ Bounce Pad and Stink Bomb Castle | Fortnite Funny Moments #67: https://www.youtube.com/watch?v=rS6m9u7WyFg NINJA Met The Most Dangerous Default Skin EVER and He's Losing!!! - Fortnite Moments #66: https://www.youtube.com/watch?v=JGNTT_sf-CQ STREAMER REACTS TO *NEW* LEGENDARY STINK BOMB IN FORTNITE | Fortnite Highlights & Funny Moments #65: https://www.youtube.com/watch?v=KXzAUn92qLg HOW TO HARVEST THE BRICK PILES AT SPAWN ISLAND | Fortnite Funny & WTF Moments #64: https://www.youtube.com/watch?v=Nbs5Dau7Ra4 NINJA LOVES THE **NEW** OMEN SKIN & ORACLE AXE!!! - Fortnite Funny & WTF Moments #63: https://www.youtube.com/watch?v=bfr_zXviMb4 "I'm calling my DAD" - Funny Fortnite Voice Chat | What's UNDER LOOT LAKE? | Fortnite #62: https://www.youtube.com/watch?v=gx5E13SaHxY When Fortnite TROLL SKYBASE: NINJA'S EPIC Bouncer Trap Kills | Fortnite Funny & WTF Moments #61: https://www.youtube.com/watch?v=4M9EeD7QVUA Ninja Wins $1,000,000 and the Golden Pickaxe!!! Fortnite Tournament & Funny Moments #60: https://www.youtube.com/watch?v=5mBUmpaLWUc NINJA Reacts to *New* LEGENDARY THERMAL SCOPED AR GAMEPLAYS and SKIN COLOR EDIT MENU | Fortnite #58: https://www.youtube.com/watch?v=yRs0wo4ffoI NINJA Squad Get Rekt by Hacker and HIS REACTION is INSANE!!! Fortnite #57: https://www.youtube.com/watch?v=s00_yfG5gIg WTF IS THIS LLAMA SPAWNER? - GLITCH OR HACK? || Fortnite Funny & WTF Moments #56: https://www.youtube.com/watch?v=RHJhCjzN9cI Level 100 Troll TRAP ELIMINATION Challenge Kill - Fortnite Battle Royale WTF Moments #55: https://youtu.be/nEbUDuxFuVs Top 50 Fortnite FAILS & FUNNY MOMENTS | Fortnite Battle Royale WTF Moments #55: https://youtu.be/9mNv1AnAHC0 INSANE *LONGEST* GOAL From PLEASANT PARK to THE STADIUM - | Fortnite Funny & WTF Moments #54: https://www.youtube.com/watch?v=MkowXSuBADg *DOUBLE JETPACKS* NEW TRICK IN FORTNITE WORLD RECORDS | Fortnite Funny & WTF Moments #53: https://www.youtube.com/watch?v=mDGBG5-7uGU NINJA *NEW* 1,000 IQ JETPACK PLAYS !! Fortnite Funny & WTF & Epic Moments #52: https://www.youtube.com/watch?v=x6qcMH4KkCM WHEN YOU CAN'T FIND ANY GUN IN FORTNITE - Fortnite Funny & WTF & Epic Moments #51: https://www.youtube.com/watch?v=w4HNMU_hDdA 100 Players Doing *STAR POWER CHALLENGE* - Fortnite Funny & WTF Moments #50: https://www.youtube.com/watch?v=8Hr_FawdW8I HOW TO TRADE *NEW* APPLES AT VENDING MACHINE - Fortnite Funny & WTF Moments #49: https://www.youtube.com/watch?v=ReXaZsLQhcI NINJA DID AN INSANE TRICK-SHOT GOAL IN FORTNITE - Fortnite Funny & WTF Moments #48: https://youtu.be/F56sOpTT4ec Doing EVERYTHING for a GOLD SCAR! - Fortnite Funny & WTF & Epic Moments #47: https://www.youtube.com/watch?v=tUzpnxhJQoo NINJA PLAYS A 9,999 IQ LAUNCH PAD *NEW* - Fortnite Funny & WTF Moments #46: https://www.youtube.com/watch?v=BDXXpmvnpj8 Have a good day :)
Views: 1497661 LuL Gaming