Home
Search results “Sql server text mining examples”
SQL Server deadlock example
 
05:14
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example_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 When can a deadlock occur In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward. The transaction that is chosen as the deadlock victim will produce the following error. Msg 1205, Level 13, State 51, Line 1 Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 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 The following 2 transactions will result in a dead lock. 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. -- 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 Next Video : We will discuss the criteria SQL Server uses to choose a deadlock victim
Views: 59616 kudvenkat
SAP HANA Academy - Text Mining: Using SQL to access Text Mining Functions [SPS 10]
 
13:40
Text Mining existed in SAP HANA SPS09, but it is easier to use in SPS10. In this video, Tahir Hussain Babar (Bob) shows how easy it is to use some of the Text Mining functions in SAP HANA SPS10. These are accessed via SQL, and in the video, Bob gives some examples of the possibilities of Text Mining in SAP HANA SPS10. Scripts ; https://github.com/saphanaacademy/TextAnalysis_Search_Mining/blob/master/TextMining_SPS10.txt Thank you for watching. Video by the SAP HANA Academy. SOCIAL MEDIA Feel free to connect with us at the links below: LinkedIn: https://linkedin.com/saphanaacademy Twitter: https://twitter.com/saphanaacademy Facebook: https://www.facebook.com/saphanaacademy/ Google+: https://plus.google.com/u/0/111935864030551244982 Github: https://github.com/saphanaacademy
Views: 3619 SAP HANA Academy
SQL Server deadlock victim selection
 
07:50
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-victim-selection.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-victim-selection_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 In this video we will discuss 1. How SQL Server detects deadlocks 2. What happens when a deadlock is detected 3. What is DEADLOCK_PRIORITY 4. What is the criteria that SQL Server uses to choose a deadlock victim when there is a deadlock This is continuation to Part 78, please watch Part 78 before proceeding. How SQL Server detects deadlocks Lock monitor thread in SQL Server, runs every 5 seconds by default to detect if there are any deadlocks. If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks. If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds. What happens when a deadlock is detected When a deadlock is detected, the Database Engine ends the deadlock by choosing one of the threads as the deadlock victim. The deadlock victim's transaction is then rolled back and returns a 1205 error to the application. Rolling back the transaction of the deadlock victim releases all locks held by that transaction. This allows the other transactions to become unblocked and move forward. What is DEADLOCK_PRIORITY By default, SQL Server chooses a transaction as the deadlock victim that is least expensive to roll back. However, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. The session with the lowest deadlock priority is chosen as the deadlock victim. Example : SET DEADLOCK_PRIORITY NORMAL DEADLOCK_PRIORITY 1. The default is Normal 2. Can be set to LOW, NORMAL, or HIGH 3. Can also be set to a integer value in the range of -10 to 10. LOW : -5 NORMAL : 0 HIGH : 5 What is the deadlock victim selection criteria 1. If the DEADLOCK_PRIORITY is different, the session with the lowest priority is selected as the victim 2. If both the sessions have the same priority, the transaction that is least expensive to rollback is selected as the victim 3. If both the sessions have the same deadlock priority and the same cost, a victim is chosen randomly SQL Script to setup the tables for the examples Create table TableA ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableA values ('Mark') Insert into TableA values ('Ben') Insert into TableA values ('Todd') Insert into TableA values ('Pam') Insert into TableA values ('Sara') Go Create table TableB ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableB values ('Mary') Go 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. We have not explicitly set DEADLOCK_PRIORITY, so both the sessions have the default DEADLOCK_PRIORITY which is NORMAL. So in this case SQL Server is going to choose Transaction 2 as the deadlock victim as it is the least expensive one to rollback. -- Transaction 1 Begin Tran Update TableA Set Name = Name + ' Transaction 1' where Id IN (1, 2, 3, 4, 5) -- From Transaction 2 window execute the first update statement Update TableB Set Name = Name + ' Transaction 1' where Id = 1 -- From Transaction 2 window execute the second update statement Commit Transaction -- Transaction 2 Begin Tran Update TableB Set Name = Name + ' Transaction 2' where Id = 1 -- From Transaction 1 window execute the second update statement Update TableA Set Name = Name + ' Transaction 2' where Id IN (1, 2, 3, 4, 5) -- After a few seconds notice that this transaction will be chosen as the deadlock -- victim as it is less expensive to rollback this transaction than Transaction 1 Commit Transaction In the following example we have set DEADLOCK_PRIORITY of Transaction 2 to HIGH. Transaction 1 will be chosen as the deadlock victim, because it's DEADLOCK_PRIORITY (Normal) is lower than the DEADLOCK_PRIORITY of Transaction 2. -- Transaction 1 Begin Tran Update TableA Set Name = Name + ' Transaction 1' where Id IN (1, 2, 3, 4, 5) -- From Transaction 2 window execute the first update statement Update TableB Set Name = Name + ' Transaction 1' where Id = 1 -- From Transaction 2 window execute the second update statement Commit Transaction
Views: 40478 kudvenkat
Phantom reads example in sql server
 
06:38
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/phantom-reads-example-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/phantom-reads-example-in-sql-server_18.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 In this video we will discuss phantom read concurrency problem with examples. Phantom read happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new row that matches the WHERE clause of the query executed by the first transaction. We will use the following table tblEmployees in this demo Scrip to create the table tblEmployees Create table tblEmployees ( Id int primary key, Name nvarchar(50) ) Go Insert into tblEmployees values(1,'Mark') Insert into tblEmployees values(3, 'Sara') Insert into tblEmployees values(100, 'Mary') Transaction 1 starts first. Reads from Emp table where Id between 1 and 3. 2 rows retrieved for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and inserts a new employee with Id = 2. Transaction 1 then makes a second read. 3 rows retrieved for second read, resulting in phantom read problem. Phantom read 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 when Transaction 1 completes, it gets different number of rows for read 1 and read 2, resulting in phantom read. -- Transaction 1 Begin Transaction Select * from tblEmployees where Id between 1 and 3 -- Do Some work waitfor delay '00:00:10' Select * from tblEmployees where Id between 1 and 3 Commit Transaction -- Transaction 2 Insert into tblEmployees values(2, 'Marcus') Serializable or any other higher isolation level should solve the phantom read problem. Fixing phantom read concurrency problem : To fix the phantom read problem, set transaction isolation level of Transaction 1 to serializable. This will place a range lock on the rows between 1 and 3, which prevents any other transaction from inserting new rows with in that range. This solves the phantom read problem. When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same number of rows. -- Transaction 1 Set transaction isolation level serializable Begin Transaction Select * from tblEmployees where Id between 1 and 3 -- Do Some work waitfor delay '00:00:10' Select * from tblEmployees where Id between 1 and 3 Commit Transaction -- Transaction 2 Insert into tblEmployees values(2, 'Marcus') Difference between repeatable read and serializable Repeatable read prevents only non-repeatable read. Repeatable read isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction, but it doe not prevent new rows from being inserted by other transactions resulting in phantom read concurrency problem. Serializable prevents both non-repeatable read and phantom read problems. Serializable isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. It also prevents new rows from being inserted by other transactions, so this isolation level prevents both non-repeatable read and phantom read problems.
Views: 49325 kudvenkat
How to find blocking queries in sql server
 
07:35
sql server find blocking processes sql server list blocking processes dbcc opentran example sql server kill spid sql server kill connections sql server kill query sql server kill transaction In this video we will discuss, how to find blocking queries in sql server. Blocking occurs if there are open transactions. Let us understand this with an example. Execute the following 2 sql statements Begin Tran Update TableA set Name='Mark Transaction 1' where Id = 1 Now from a different window, execute any of the following commands. Notice that all the queries are blocked. Select Count(*) from TableA Delete from TableA where Id = 1 Truncate table TableA Drop table TableA This is because there is an open transaction. Once the open transaction completes, you will be able to execute the above queries. So the obvious next question is - How to identify all the active transactions. One way to do this is by using DBCC OpenTran. DBCC OpenTran will display only the oldest active transaction. It is not going to show you all the open transactions. DBCC OpenTran The following link has the SQL script that you can use to identify all the active transactions. http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans The beauty about this script is that it has a lot more useful information about the open transactions Session Id Login Name Database Name Transaction Begin Time The actual query that is executed You can now use this information and ask the respective developer to either commit or rollback the transactions that they have left open unintentionally. For some reason if the person who initiated the transaction is not available, you also have the option to KILL the associated process. However, this may have unintended consequences, so use it with extremen caution. There are 2 ways to kill the process are described below Killing the process using SQL Server Activity Monitor : 1. Right Click on the Server Name in Object explorer and select "Activity Monitor" 2. In the "Activity Monitor" window expand Processes section 3. Right click on the associated "Session ID" and select "Kill Process" from the context menu Killing the process using SQL command : KILL Process_ID What happens when you kill a session All the work that the transaction has done will be rolled back. The database must be put back in the state it was in, before the transaction started. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/how-to-find-blocking-queries-in-sql_3.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/how-to-find-blocking-queries-in-sql_3.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: 50182 kudvenkat
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: 31092 kudvenkat
Create Report in Seconds by Fetching Data from SQL Server using Excel VBA
 
11:34
Create Report in Seconds by Fetching Data from SQL Server using Excel VBA If your manager needs the report very frequently then you need to do the same task again and again. Frankly speaking, I faced this kind of situation in my previous company and I prepared the report using VBA and handed over the file to my manager. Now, whenever he will click the button he will get the report of the LIVE DATA from SQL to Excel. You can read our blog to go through instructions as well as download working and code files, Click here: http://yodalearning.com/tutorials/export-data-from-sql-to-excel-spreadsheet-using-vba/ You can enroll in our Excel VBA course: http://courses.yodalearning.com/p/excel-vba-tutorials CHECK SOME OF THE FREE COURSES WE OFFER http://courses.yodalearning.com/p/free-office-2016-tips Keep yourself updated. Follow us now! http://www.facebook.com/yodalearning http://www.twitter.com/yodalearning
Views: 86159 Excel Yoda learning
SQL Server Programming Part 11 - Common Table Expressions (CTEs)
 
08:49
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - A Common Table Expression in SQL is a technique for creating a temporary recordset that you immediately use in another statement - they're often a neat alternative to using Temporary Tables. In this video we'll teach you how to create and consume a CTE, show you a couple of practical uses for them and hopefully convince you that they're worth using! You can see a written version of this tutorial at: https://www.wiseowl.co.uk/blog/s184/cte-sql.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 39065 WiseOwlTutorials
SQL Server Queries Part 5 - CASE Expressions
 
07:48
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - Learn how to use CASE expressions in queries in Microsoft SQL Server. This video teaches you how to test conditions using a CASE expression and provide different answers based on whether the conditions are met. A written version of this tutorial can be found at: https://www.wiseowl.co.uk/blog/s330/case-when.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 120830 WiseOwlTutorials
Non repeatable read example in sql server
 
05:45
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/non-repeatable-read-example-in-sql.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/non-repeatable-read-example-in-sql_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 In this video we will discuss non repeatable read concurrency problem with an example. Non repeatable read problem happens when one transaction reads the same data twice and another transaction updates that data in between the first and second read of transaction one. Transaction 1 starts first. Reads ItemsInStock. Gets a value of 10 for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and UpdatesItemsInStock to 5. Transaction 1 then makes a second read. At this point Transaction 1 gets a value of 5, reulting in non-repeatable read problem. Non-repeatable read 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 when Transaction 1 completes, it gets different values for read 1 and read 2, resulting in non-repeatable read. -- Transaction 1 Begin Transaction Select ItemsInStock from tblInventory where Id = 1 -- Do Some work waitfor delay '00:00:10' Select ItemsInStock from tblInventory where Id = 1 Commit Transaction -- Transaction 2 Update tblInventory set ItemsInStock = 5 where Id = 1 Repeatable read or any other higher isolation level should solve the non-repeatable read problem. Fixing non repeatable read concurrency problem : To fix the non-repeatable read problem, set transaction isolation level of Transaction 1 to repeatable read. This will ensure that the data that Transaction 1 has read, will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read problem. When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same value for ItemsInStock. -- Transaction 1 Set transaction isolation level repeatable read Begin Transaction Select ItemsInStock from tblInventory where Id = 1 -- Do Some work waitfor delay '00:00:10' Select ItemsInStock from tblInventory where Id = 1 Commit Transaction -- Transaction 2 Update tblInventory set ItemsInStock = 5 where Id = 1
Views: 49836 kudvenkat
Tutorial de Text Mining
 
01:04:01
Views: 61 Linc ufpa
SQL Server deadlock analysis and prevention
 
08:58
sql server deadlock analysis read sql server deadlock log Read and analyze sql server deadlock log Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-analysis-and.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-analysis-and_28.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 In this video we will discuss how to read and analyze sql server deadlock information captured in the error log, so we can understand what's causing the deadlocks and take appropriate actions to prevent or minimize the occurrence of deadlocks. This is continuation to Part 80. Please watch Part 80 from SQL Server tutorial before proceeding. The deadlock information in the error log has three sections 1. Deadlock Victim - Contains the ID of the process that was selected as the deadlock victim and killed by SQL Server. 2. Process List - Contains the list of the processes that participated in the deadlock. 3. Resource List - Contains the list of the resources (database objects) owned by the processes involved in the deadlock. Process List : The process list has lot of items. Here are some of them that are particularly useful in understanding what caused the deadlock. loginname : The loginname associated with the process isolationlevel : What isolation level is used procname: The stored procedure name Inputbuf: The code the process is executing when the deadlock occured Resource List : Some of the items in the resource list that are particularly useful in understanding what caused the deadlock. objectname : Fully qualified name of the resource involved in the deadlock owner-list : Contains (owner id) the id of the owning process and the lock mode it has acquired on the resource. lock mode determines how the resource can be accessed by concurrent transactions. S for Shared lock, U for Update lock, X for Exclusive lock etc. waiter-list : Contains (waiter id) the id of the process that wants to acquire a lock on the resource and the lock mode it is requesting. To prevent the deadlock that we have in our case, we need to ensure that database objects (Table A & Table B) are accessed in the same order every time.
Views: 44354 kudvenkat
Sql server query plan cache
 
14:20
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/sql-server-query-plan-cache.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/sql-server-query-plan-cache_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 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 happens when a query is issued to SQL Server 2. How to check what is in SQL Server plan cache 3. Things to consider to promote query plan reusability What happens when a query is issued to SQL Server In SQl Server, every query requires a query plan before it is executed. When you run a query the first time, the query gets compiled and a query plan is generated. This query plan is then saved in sql server query plan cache. Next time when we run the same query, the cached query plan is reused. This means sql server does not have to create the plan again for that same query. So reusing a query plan can increase the performance. How long the query plan stays in the plan cache depends on how often the plan is reused besides other factors. The more often the plan is reused the longer it stays in the plan cache. How to check what is in SQL Server plan cache SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY cp.usecounts DESC As you can see we have sorted the result set by usecounts column in descending order, so we can see the most frequently reused query plans on the top. usecounts - Number of times the plan is reused objtype - Specifies the type of object text - Text of the SQL query query_plan - Query execution plan in XML format To remove all elements from the plan cache use the following command DBCC FREEPROCCACHE In older versions of SQL Server up to SQL Server 6.5 only stored procedure plans are cached. The query plans for Adhoc sql statements or dynamic sql statements are not cached, so they get compiled every time. With SQL Server 7, and later versions the query plans for Adhoc sql statements and dynamic sql statements are also cached. Things to consider to promote query plan reusability For example, when we execute the following query the first time. The query is compiled, a plan is created and put in the cache. Select * From Employees Where FirstName = 'Mark' When we execute the same query again, it looks up the plan cache, and if a plan is available, it reuses the existing plan instead of creating the plan again which can improve the performance of the query. However, one important thing to keep in mind is that, the cache lookup is by a hash value computed from the query text. If the query text changes even slightly, sql server will not be able to reuse the existing plan. For example, even if you include an extra space somewhere in the query or you change the case, the query text hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan. Another example : If you want the same query to find an employee whose FirstName is Steve instead of Mark. You would issue the following query Select * From Employees Where FirstName = 'Steve' Even in this case, since the query text has changed the hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan. This is why, it is very important to use parameterised queries for sql server to be able to reuse cached query plans. With parameterised queries, sql server will not treat parameter values as part of the query text. So when you change the parameters values, sql server can still reuse the cached query plan. The following query uses parameters. So even if you change parameter values, the same query plan is reused. Declare @FirstName nvarchar(50) Set @FirstName = 'Steve' Execute sp_executesql N'Select * from Employees where [email protected]', N'@FN nvarchar(50)', @FirstName One important thing to keep in mind is that, when you have dynamic sql in a stored procedure, the query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own. Summary: Never ever concatenate user input values with strings to build dynamic sql statements. Always use parameterised queries which not only promotes cached query plans reuse but also prevent sql injection attacks.
Views: 24045 kudvenkat
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: 49268 kudvenkat
Explore FileTables, Full-Text Search and Semantic Search Across Unstructured Data in SQL Server 2012
 
19:54
Michael Rys, Principal Program Manager, Microsoft,
Views: 5947 sqlserver
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: 54503 kudvenkat
SQL Server Queries Part 11 - Subqueries
 
08:51
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - The idea of a subquery is pretty simple: it's just one query nested inside another. If that doesn't sound particularly useful then you should watch this video to find out why it is! You'll learn how to place a subquery in a SELECT list or a WHERE clause, how to add criteria to a subquery, and how to handle subqueries which return multiple values. You can view a written version of this tutorial here: https://www.wiseowl.co.uk/blog/s339/subquery.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 87246 WiseOwlTutorials
Cube in SQL Server
 
07:42
group by cube in sql server 2008 cube group by sql sql server group by cube example sql server cube group by Cube() in SQL Server produces the result set by generating all combinations of columns specified in GROUP BY CUBE(). Let us understand Cube() in SQL Server with examples. Write a query to retrieve Sum of Salary grouped by all combinations of the following 2 columns as well as Grand Total. Country, Gender Using Cube with Group By SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Cube(Country, Gender) --OR SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country, Gender with Cube The above query is equivalent to the following Grouping Sets query. SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY GROUPING SETS ( (Country, Gender), (Country), (Gender), () ) The above query is equivalent to the following UNION ALL query. While the data in the result set is the same, the ordering is not. Use ORDER BY to control the ordering of rows in the result set. 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, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Gender UNION ALL SELECT NULL, NULL, SUM(Salary) AS TotalSalary FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/cube-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/cube-in-sql-server_22.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: 58067 kudvenkat
Semantic Text Processing: Example Application
 
02:06
This example application shows how Semantic Technology and Text Processing (Text Mining) can be used together to deliver semantic text processing. This application compares two legal documents (e.g. service contracts) and referring to the word semantics (actual meaning) looks for sentences that mean the same. This could not be achieved only with statistical methods and keyword-based comparison. This demo application was build using Ontorion Server SDK. To learn more visit: http://www.cognitum.eu/semantics/
Views: 779 cognitumeu
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: 57916 kudvenkat
sql server lost update problem
 
08:29
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-lost-update-problem.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-lost-update-problem_16.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 In this video we will discuss, lost update problem in sql server with an example. Lost update problem happens when 2 transactions read and update the same data. Let's understand this with an example. Consider the following 2 transactions - Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 iPhone. It sees ItemsInStock as 10. At this time Transaction 2 is processing another order for 2 iPhones. It also sees ItemsInStock as 10. Transaction 2 makes the sale first and updates ItemsInStock with a value of 8. At this point Transaction 1 completes the sale and silently overwrites the update of Transaction 2. As Transaction 1 sold 1 iPhone it has updated ItemsInStock to 9, while it actually should have updated it to 7. Example : The lost update problem 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. Transaction 1 is processing an order for 1 iPhone, while Transaction 2 is processing an order for 2 iPhones. At the end of both the transactions ItemsInStock must be 7, but we have a value of 9. This is because Transaction 1 silently overwrites the update of Transaction 2. This is called the lost update problem. -- Transaction 1 Begin Tran Declare @ItemsInStock int Select @ItemsInStock = ItemsInStock from tblInventory where Id=1 -- Transaction takes 10 seconds Waitfor Delay '00:00:10' Set @ItemsInStock = @ItemsInStock - 1 Update tblInventory Set ItemsInStock = @ItemsInStock where Id=1 Print @ItemsInStock Commit Transaction -- Transaction 2 Begin Tran Declare @ItemsInStock int Select @ItemsInStock = ItemsInStock from tblInventory where Id=1 -- Transaction takes 1 second Waitfor Delay '00:00:1' Set @ItemsInStock = @ItemsInStock - 2 Update tblInventory Set ItemsInStock = @ItemsInStock where Id=1 Print @ItemsInStock Commit Transaction Both Read Uncommitted and Read Committed transaction isolation levels have the lost update side effect. Repeatable Read, Snapshot, and Serializable isolation levels does not have this side effect. If you run the above Transactions using any of the higher isolation levels (Repeatable Read, Snapshot, or Serializable) you will not have lost update problem. The repeatable read isolation level uses additional locking on rows that are read by the current transaction, and prevents them from being updated or deleted elsewhere. This solves the lost update problem. For both the above transactions, set Repeatable Read Isolation Level. Run Transaction 1 first and then a few seconds later run Transaction 2. Transaction 1 completes successfully, but Transaction 2 competes with the following error. Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Once you rerun Transaction 2, ItemsInStock will be updated correctly as expected.
Views: 54112 kudvenkat
Predictive Analytics with R Server and SQL Server 2016
 
01:01:44
They key to optimizing any business is to match demand with supply. Using Microsoft R Server and SQL Server 2016 we can forecast NYC taxi demand using the freely available NYC taxi data for the year 2013. Watch as we use the power of the R programming language in conjunction with Microsoft SQL Server 2016 to transform and prepare large amounts of data into a format that is ideal for building and deploying a predictive forecasting model.
Views: 20931 Pragmatic Works
Data Mining in SQL Server Analysis Services
 
01:29:25
Presenter: Brian Knight
Views: 97776 PASStv
Read committed snapshot isolation level in sql server
 
06:58
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/read-committed-snapshot-isolation-level.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/read-committed-snapshot-isolation-level_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 In this video we will discuss Read committed snapshot isolation level in sql server. This is continuation Part 75. Please watch Part 75 from SQL Server tutorial before proceeding. Read committed snapshot isolation level is not a different isolation level. It is a different way of implementing Read committed isolation level. One problem we have with Read Committed isloation level is that, it blocks the transaction if it is trying to read the data, that another transaction is updating at the same time. The following example demonstrates the above point. 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 until Transaction 1 is completed. --Transaction 1 Set transaction isolation level Read Committed Begin Transaction Update tblInventory set ItemsInStock = 5 where Id = 1 waitfor delay '00:00:10' Commit Transaction -- Transaction 2 Set transaction isolation level read committed Begin Transaction Select ItemsInStock from tblInventory where Id = 1 Commit Transaction We can make Transaction 2 to use row versioning technique instead of locks by enabling Read committed snapshot isolation at the database level. Use the following command to enable READ_COMMITTED_SNAPSHOT isolation Alter database SampleDB SET READ_COMMITTED_SNAPSHOT ON Please note : For the above statement to execute successfully all the other database connections should be closed. After enabling READ_COMMITTED_SNAPSHOT, execute Transaction 1 first and then Transaction 2 simultaneously. Notice that the Transaction 2 is not blocked. It immediately returns the committed data that is in the database before Transaction 1 started. This is because Transaction 2 is now using Read committed snapshot isolation level. Let's see if we can achieve the same thing using snapshot isolation level instead of read committed snapshot isolation level. Step 1 : Turn off READ_COMMITTED_SNAPSHOT Alter database SampleDB SET READ_COMMITTED_SNAPSHOT OFF Step 2 : Enable snapshot isolation level at the database level Alter database SampleDB SET ALLOW_SNAPSHOT_ISOLATION ON Step 3 : Execute Transaction 1 first and then Transaction 2 simultaneously. Just like in the previous example, notice that the Transaction 2 is not blocked. It immediately returns the committed data that is in the database before Transaction 1 started. --Transaction 1 Set transaction isolation level Read Committed Begin Transaction Update tblInventory set ItemsInStock = 5 where Id = 1 waitfor delay '00:00:10' Commit Transaction -- Transaction 2 Set transaction isolation level snapshot Begin Transaction Select ItemsInStock from tblInventory where Id = 1 Commit Transaction So what is the point in using read committed snapshot isolation level over snapshot isolation level. There are some differences between read committed snapshot isolation level and snapshot isolation level. We will discuss these in our next video.
Views: 44045 kudvenkat
LAST VALUE function in SQL Server
 
05:35
last_value function in sql server 2008 sql server last_value function returns incorrect data sql server last_value function example sql server last_value function with partition example LAST_VALUE function in SQL Server In this video we will discuss LAST_VALUE function in SQL Server. LAST_VALUE function Introduced in SQL Server 2012 Retrieves the last value from the specified column ORDER BY clause is required PARTITION BY clause is optional ROWS or RANGE clause is optional, but for it to work correctly you may have to explicitly specify a value Syntax : LAST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...) LAST_VALUE function not working as expected : In the following example, LAST_VALUE function does not return the name of the highest paid employee. This is because we have not specified an explicit value for ROWS or RANGE clause. As a result it is using it's default value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (ORDER BY Salary) AS LastValue FROM Employees LAST_VALUE function working as expected : In the following example, LAST_VALUE function returns the name of the highest paid employee as expected. Notice we have set an explicit value for ROWS or RANGE clause to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING This tells the LAST_VALUE function that it's window starts at the first row and ends at the last row in the result set. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM Employees LAST_VALUE function example with partitions : In the following example, LAST_VALUE function returns the name of the highest paid employee from the respective partition. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server_9.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: 32809 kudvenkat
SQL Server deadlock example in arabic
 
08:14
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example_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 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: 212 kudvenkat.arabic
MSPTDA 11: Power Query to Import from SQL Server Database in Excel or Power BI Desktop
 
19:11
Download Excel START File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MSPTDA-ImportSQLIntoPowerQuery-Start.xlsx Download Excel FINISHED File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MSPTDA-ImportSQLIntoPowerQuery-Finished.xlsx Download Power BI Desktop FINISHED File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MPSTDA-PowerBIDesktop-GetSQL-Data.pbix Download pdf Notes about Power Query: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MSPTDA-PowerQueryImportSQLData.pdf Practice Problems: Assigned Homework: Download homework file (Practice Problems) : https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MSPTDA-Homework-Start.xlsx Example of Finished Homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/011-MSPTDA-Homework-Finished.xlsx link to Microsoft Notes on Direct Query: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery In this Video learn how to connect to an SQL Server Database and extract and transform data using Power Query in Excel and Power BI Desktop. Topics: 1. (00:16) Introduction 2. (00:32) What is an SQL Server Database 3. (02:19) The Goal of our Queries and a look at the end result reports in Excel 4. (03:04) Comparing and Contrast using 1) Using Power Query User Interface or 2) Writing SQL Code in Power Query 5. (04:46) Example 1: Use Power Query User Interface to connect to SQL Server and Extract, Transform and Load Data. 6. (11:27) Example 2: Write SQL Code to connect to SQL Server and Extract, Transform and Load Data. 7. (14:44) Example 3: Using Power BI Desktop to connect to SQL Server and Import multiple Tables. 8. (18:29) Summary Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Views: 5938 ExcelIsFun
SQL Server Programming Part 3 - Variables
 
19:08
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - Using variables allows you to give your SQL procedures a memory. You can store a value in a variable and then retrieve and reuse that value at any point later in the same procedure. This video teaches you how to declare variables, assign values to them and make use of those values in a query. You'll also see how to store the result of a SELECT statement in a variable, as well as how to accumulate the value of a variable. You'll see a couple of methods of displaying the value stored in a variable and we'll end with a quick overview of the global variables in SQL Server. SQL variables are explained in written form here: https://www.wiseowl.co.uk/blog/s333/sql-variable.htm Global variables are explained in more detail on this page: https://www.wiseowl.co.uk/blog/s338/global-variables.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 100036 WiseOwlTutorials
SQL Server Spatial Tools Introduction
 
18:52
Learn how to use SQL Server Spatial Tool to create and using maps. Watch other video tutorials, visit http://www.learn-with-video-tutorials.com
sql server trigger execution order
 
06:43
sql server set trigger order sp_settriggerorder in sql server sp_settriggerorder example exec sp_settriggerorder In this video we will discuss how to set the execution order of triggers using sp_settriggerorder stored procedure. Server scoped triggers will always fire before any of the database scoped triggers. This execution order cannot be changed. In the example below, we have a database-scoped and a server-scoped trigger handling the same event (CREATE_TABLE). When you create a table, notice that server-scoped trigger is always fired before the database-scoped trigger. CREATE TRIGGER tr_DatabaseScopeTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN Print 'Database Scope Trigger' END GO CREATE TRIGGER tr_ServerScopeTrigger ON ALL SERVER FOR CREATE_TABLE AS BEGIN Print 'Server Scope Trigger' END GO Using the sp_settriggerorder stored procedure, you can set the execution order of server-scoped or database-scoped triggers. sp_settriggerorder stored procedure has 4 parameters @triggername - Name of the trigger @order - value can be First, Last or None. When set to None, trigger is fired in random order @stmttype - SQL statement that fires the trigger. Can be INSERT, UPDATE, DELETE or any DDL event @namespace - Scope of the trigger. Value can be DATABASE, SERVER, or NULL EXEC sp_settriggerorder @triggername = 'tr_DatabaseScopeTrigger1', @order = 'none', @stmttype = 'CREATE_TABLE', @namespace = 'DATABASE' GO If you have a database-scoped and a server-scoped trigger handling the same event, and if you have set the execution order at both the levels. Here is the execution order of the triggers. 1. The server-scope trigger marked First 2. Other server-scope triggers 3. The server-scope trigger marked Last 4. The database-scope trigger marked First 5. Other database-scope triggers 6. The database-scope trigger marked Last Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-trigger-execution-order.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-trigger-execution-order_11.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: 27726 kudvenkat
Boost your Analytics with Machine Learning for SQL Nerds
 
01:10:38
Are you trying to understand what’s the fuss around Machine Learning? Are you working with a lot of data analysis and wondered how SQL Server 2016 R Services might be able to help? This session will provide a gentle and fun introduction to SQL Server R Services for SQL Nerds. We will walk through pragmatic examples of using R through demos, to illustrate Machine Learning in boosting your Data Analysis capabilities. The goal of this session is to provide quick tips and tricks for attendees to get started on SQL Server R Services, either for fun or for work.
SQL Server DBA Interview Questions | When should we update Statistics on SQL Server Database and why
 
05:53
In this video you will learn the answer of SQL Server DBA Interview Questions "When should we update Statistics on SQL Server Database and why?" Complete list of SQL Server DBA Interview Questions by Tech Brothers http://sqlage.blogspot.com/search/label/SQL%20SERVER%20DBA%20INTERVIEW%20QUESTIONS
Views: 33343 TechBrothersIT
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: 32373 kudvenkat
SQL Server Programming Part 13 - Dynamic SQL
 
27:41
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - Dynamic SQL allows you to build a complete statement out of individual strings of text and execute it as though it was an SQL statement. It allows you to create immensely flexible queries in which any part of a statement can be parameterised but it can also leave you vulnerable to the dreaded SQL injection attack! This video teaches you how to build dynamic SQL statements, how to use stored procedures to parameterise the process, and the potential dangers of using dynamic SQL in a live system. You can view a written version of this tutorial at: https://www.wiseowl.co.uk/blog/s363/dynamic-sql.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip For more information on SQL injection attacks and how to prevent them you can't do better than Erland Sommarskog's definitive article: http://www.sommarskog.se/dynamic_sql.html For a geeky joke about dynamic SQL see XKCD: https://xkcd.com/327/ You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 59405 WiseOwlTutorials
MDX Query Basics (Analysis Services 2012)
 
13:09
This video is part of LearnItFirst's SQL Server 2012: A Comprehensive Introduction course. More information on this video and course is available here: http://www.learnitfirst.com/Course170 In this video, we walk through the basics of the MDX Query language. It is a very logical language, however, is somewhat large in syntax. If you enjoy writing Transact-SQL, you will really enjoy the MDX language. The AdventureWorks2012 multidimensional models need to be installed on your SSAS Multidimensional mode instance from the CodePlex web site. Highlights from this video: - The basics of an MDX query - What is the basic format of the MDX query language? - Is it necessary to have a WHERE clause in an MDX query? - How to signal the end of a statement in the MDX query language - Using the Internet Order Count and much more...
Views: 103039 LearnItFirst.com
David Itzhak:  Practical SQL 2014 Data Mining and introduction to R
 
01:22:58
Microsoft have acquired Revolution Analytics in order to help customers to find their "Big Data" value. Revolution Analytics is the leading commercial provider of software and services for R, the world’s most widely used programming language for statistical computing and predictive analytics. R will be integrated to SQL 2016 BI. I will give introduction to R and the advantages it gives to Microsoft Data Mining. • An introduction to data mining • SQL Server Data Tools (SSDT) Installations • Demo : Creating a data mining solution with Microsoft association rules • DEMO : Decision Trees example- In short • Prediction with MDX queries , No need to know the syntax . • Identifying Data Patterns – Predictive Models in SSAS
SQL Server Programming Part 17 - The Pivot Operator
 
13:53
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - The Pivot Operator in Microsoft SQL Server allows you to group data by columns, as well as by rows, to create simple static pivot tables. This video teaches you how to use the pivot operator including how to apply aggregate functions and, importantly, a quick method for listing out the column headings of your pivot table using the QuoteName function. You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can view a written version of this tutorial at: https://www.wiseowl.co.uk/blog/s417/pivoting-sql-data.htm You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 41038 WiseOwlTutorials
SQL Server deadlock analysis and prevention in arabic
 
16:02
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-analysis-and.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-analysis-and_28.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: 189 kudvenkat.arabic
Import Data and Analyze with MATLAB
 
09:19
Data are frequently available in text file format. This tutorial reviews how to import data, create trends and custom calculations, and then export the data in text file format from MATLAB. Source code is available from http://apmonitor.com/che263/uploads/Main/matlab_data_analysis.zip
Views: 359206 APMonitor.com
Difference between cube and rollup in SQL Server
 
05:38
difference between cube and rollup in sql server 2008 sql server rollup vs cube difference between cube and rollup in sql server 2005 rollup and cube in sql server 2005 In this video we will discuss the difference between cube and rollup in SQL Server. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns, where as ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns. Let us understand this difference with an example. ROLLUP(Continent, Country, City) produces Sum of Salary for the following hierarchy Continent, Country, City Continent, Country, Continent () CUBE(Continent, Country, City) produces Sum of Salary for all the following column combinations Continent, Country, City Continent, Country, Continent, City Continent Country, City Country, City () SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY CUBE(Continent, Country, City) You won't see any difference when you use ROLLUP and CUBE on a single column. Both the following queries produces the same output. SELECT Continent, Sum(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent) -- OR SELECT Continent, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY CUBE(Continent) Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-cube-and-rollup-in.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-cube-and-rollup-in_24.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: 43044 kudvenkat
SQL Server Integration Services (SSIS) Part 15 - Script Components
 
19:06
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andy Brown https://www.wiseowl.co.uk - This tutorial explains how you can create script components to validate and transform data within data flow tasks in Integration Services. The tutorial uses C# as its programming language, but includes how to find the script in Visual Basic. If you like the style of this video (but agree with us that classroom training is more effective) have a look at our classroom-based training courses at www.wiseowl.co.uk. These include a two-day SSIS course, as well as courses in Reporting Services and Analysis Services.
Views: 45757 WiseOwlTutorials
Data Mining using R | Data Mining Tutorial for Beginners | R Tutorial for Beginners | Edureka
 
36:36
( R Training : https://www.edureka.co/r-for-analytics ) This Edureka R tutorial on "Data Mining using R" will help you understand the core concepts of Data Mining comprehensively. This tutorial will also comprise of a case study using R, where you'll apply data mining operations on a real life data-set and extract information from it. Following are the topics which will be covered in the session: 1. Why Data Mining? 2. What is Data Mining 3. Knowledge Discovery in Database 4. Data Mining Tasks 5. Programming Languages for Data Mining 6. Case study using R Subscribe to our channel to get video updates. Hit the subscribe button above. Check our complete Data Science playlist here: https://goo.gl/60NJJS #LogisticRegression #Datasciencetutorial #Datasciencecourse #datascience How it Works? 1. There will be 30 hours of instructor-led interactive online classes, 40 hours of assignments and 20 hours of project 2. We have a 24x7 One-on-One LIVE Technical Support to help you with any problems you might face or any clarifications you may require during the course. 3. You will get Lifetime Access to the recordings in the LMS. 4. At the end of the training you will have to complete the project based on which we will provide you a Verifiable Certificate! - - - - - - - - - - - - - - About the Course Edureka's Data Science course will cover the whole data life cycle ranging from Data Acquisition and Data Storage using R-Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities. - - - - - - - - - - - - - - Why Learn Data Science? Data Science training certifies you with ‘in demand’ Big Data Technologies to help you grab the top paying Data Science job title with Big Data skills and expertise in R programming, Machine Learning and Hadoop framework. After the completion of the Data Science course, you should be able to: 1. Gain insight into the 'Roles' played by a Data Scientist 2. Analyse Big Data using R, Hadoop and Machine Learning 3. Understand the Data Analysis Life Cycle 4. Work with different data formats like XML, CSV and SAS, SPSS, etc. 5. Learn tools and techniques for data transformation 6. Understand Data Mining techniques and their implementation 7. Analyse data using machine learning algorithms in R 8. Work with Hadoop Mappers and Reducers to analyze data 9. Implement various Machine Learning Algorithms in Apache Mahout 10. Gain insight into data visualization and optimization techniques 11. Explore the parallel processing feature in R - - - - - - - - - - - - - - Who should go for this course? The course is designed for all those who want to learn machine learning techniques with implementation in R language, and wish to apply these techniques on Big Data. The following professionals can go for this course: 1. Developers aspiring to be a 'Data Scientist' 2. Analytics Managers who are leading a team of analysts 3. SAS/SPSS Professionals looking to gain understanding in Big Data Analytics 4. Business Analysts who want to understand Machine Learning (ML) Techniques 5. Information Architects who want to gain expertise in Predictive Analytics 6. 'R' professionals who want to captivate and analyze Big Data 7. Hadoop Professionals who want to learn R and ML techniques 8. Analysts wanting to understand Data Science methodologies Please write back to us at [email protected] or call us at +918880862004 or 18002759730 for more information. Website: https://www.edureka.co/data-science Facebook: https://www.facebook.com/edurekaIN/ Twitter: https://twitter.com/edurekain LinkedIn: https://www.linkedin.com/company/edureka Customer Reviews: Gnana Sekhar Vangara, Technology Lead at WellsFargo.com, says, "Edureka Data science course provided me a very good mixture of theoretical and practical training. The training course helped me in all areas that I was previously unclear about, especially concepts like Machine learning and Mahout. The training was very informative and practical. LMS pre recorded sessions and assignmemts were very good as there is a lot of information in them that will help me in my job. The trainer was able to explain difficult to understand subjects in simple terms. Edureka is my teaching GURU now...Thanks EDUREKA and all the best. " Facebook: https://www.facebook.com/edurekaIN/ Twitter: https://twitter.com/edurekain LinkedIn: https://www.linkedin.com/company/edureka
Views: 58804 edureka!
Microsoft SQL Server Performance Tuning, Live
 
54:30
Brent's session at Microsoft Ignite 2015 in Chicago. He covers trace flag 4199, columnstore indexes, scaling out to AG secondaries, Cardinality Estimator changes, delayed durability, in-memory OLTP, Buffer Pool Extensions, and more.
SQL Server Programming Part 6 - WHILE Loops
 
14:48
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - In SQL Server there is only one type of loop: a WHILE loop. This video teaches you how to use them, from the basic syntax of the WHILE statement, through how to use a SELECT statement within a loop. We'll also cover how to use the BREAK command to exit from a loop, what to do when you inevitably find yourself in an endless loop and, finally, a quick introduction to using loops with cursors. You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 71937 WiseOwlTutorials
Indexes in SQL Server -- making queries run faster
 
10:57
Indexes in SQL Server make queries run faster and more efficiently. With the size of databases increasing almost exponentially, being able to return results quickly with minimal stress on the underlying server is crucial. This tutorial includes a practical demonstration of performance gains obtained from creating Clustered, Non-Clustered, and Column indexes. In the demonstration clustered and non-clustered indexes result in a 10 x increase in query performance compared to using no index. The Column index results in further a 10 x increase in performance over the Clustered and Non-clustered indexes (100 x faster than using no index). The video also outlines the basic concepts behind indexes and demonstrates how to quickly created indexes in SQL Server Management studio. Code and sample databases are available from http://www.analyticsinaction.com/indexes/ I also have a comprehensive 60 minute T-SQL course available at Udemy : https://www.udemy.com/t-sql-for-data-analysts/?couponCode=ANALYTICS50%25OFF
Views: 46307 Steve Fox
Reverse PIVOT table in SQL Server
 
08:16
convert rows to columns sql pivot and unpivot table sql server pivot and unpivot examples in sql server undo pivot table sql server In this video we will discuss if it's always possible to reverse what PIVOT operator has done using UNPIVOT operator. Is it always possible to reverse what PIVOT operator has done using UNPIVOT operator. No, not always. If the PIVOT operator has not aggregated the data, you can get your original data back using the UNPIVOT operator but not if the data is aggregated. Let us understand this with an example. We will use the following table tblProductSales for the examples in this video. SQL Script to create tblProductSales table Create Table tblProductSales ( SalesAgent nvarchar(10), Country nvarchar(10), SalesAmount int ) Go Insert into tblProductSales values('David','India',960) Insert into tblProductSales values('David','US',520) Insert into tblProductSales values('John','India',970) Insert into tblProductSales values('John','US',540) Go Let's now use the PIVOT operator to turn ROWS into COLUMNS SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable Now let's use the UNPIVOT operator to reverse what PIVOT operator has done. SELECT SalesAgent, Country, SalesAmount FROM (SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable) P UNPIVOT ( SalesAmount FOR Country IN (India, US) ) AS UnpivotTable The above query reverses what PIVOT operator has done, and we get the original data back as shown below. We are able to get the original data back, because the SUM aggregate function that we used with the PIVOT operator did not perform any aggregation. Now execute the following INSERT statement to insert a new row into tblProductSales table. Insert into tblProductSales values('David','India',100) With this new row in the table, if you execute the following PIVOT query data will be aggregated SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable Now if we use UNPIVOT opertaor with the above query, we wouldn't get our orginial data back as the PIVOT operator has already aggrgated the data, and there is no way for SQL Server to know how to undo the aggregations. SELECT SalesAgent, Country, SalesAmount FROM (SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable) P UNPIVOT ( SalesAmount FOR Country IN (India, US) ) AS UnpivotTable Notice that for SalesAgent - David and Country - India we get only one row. In the original table we had 2 rows for the same combination. 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/10/reverse-pivot-table-in-sql-server.html
Views: 27244 kudvenkat
How to Import a Flat Text File Using SSIS 2012
 
14:54
This video is part of LearnItFirst's SQL Server 2012: A Comprehensive Introduction course. More information on this video and course is available here: http://www.learnitfirst.com/Course170 In this video, Instructor Scott Whigham will explain what a Flat Text File is, why it is important for sharing data between programs, and show you step by step of how to import a Flat Text File using the SQL Server Import and Export Wizard in SQL Server Management Studio. Highlights from this video: - Step by step importing a Flat File into SSMS with the Import and Export Wizard - What is a "Flat File," and what is the benefit of using one? - Setting the row and column delimiters - Will the Import and Export Wizard infer a primary key in a Flat Text File or Excel File? - What is a text qualifier? and much more...
Views: 55190 LearnItFirst.com
RapidMiner 5 Tutorial - Video 11 - Integration With SQL Server
 
04:39
How to connect RapidMiner with Microsoft SQL Server: Connecting to a named instance, Connecting using Windows authentication, Using the Read Data operator, as well as Import Data Vancouver Data Blog http://vancouverdata.blogspot.com/
Views: 12843 el chief
Capturing deadlocks in sql profiler
 
10:21
deadlock graph sql server 2008 how can you get a deadlock graph from sql server sql server profiler deadlock graphs sql server deadlock graph xml how to read deadlock graph deadlock graph analysis deadlock graph analyze analyze deadlock graph sql server Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/capturing-deadlocks-in-sql-profiler.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/capturing-deadlocks-in-sql-profiler_29.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 In this video we will discuss how to capture deadlock graph using SQL profiler. To capture deadlock graph, all you need to do is add Deadlock graph event to the trace in SQL profiler. Here are the steps : 1. Open SQL Profiler 2. Click File - New Trace. Provide the credentials and connect to the server 3. On the general tab, select "Blank" template from "Use the template" dropdownlist 4. On the "Events Selection" tab, expand "Locks" section and select "Deadlock graph" event 5. Finally click the Run button to start the trace 6. At this point execute the code that causes deadlock 7. The deadlock graph should be captured in the profiler The deadlock graph data is captured in XML format. If you want to extract this XML data to a physical file for later analysis, you can do so by following the steps below. 1. In SQL profiler, click on "File - Export - Extract SQL Server Events - Extract Deadlock Events" 2. Provide a name for the file 3. The extension for the deadlock xml file is .xdl 4. Finally choose if you want to export all events in a single file or each event in a separate file The deadlock information in the XML file is similar to what we have captured using the trace flag 1222. Analyzing the deadlock graph 1. The oval on the graph, with the blue cross, represents the transaction that was chosen as the deadlock victim by SQL Server. 2. The oval on the graph represents the transaction that completed successfully. 3. When you move the mouse pointer over the oval, you can see the SQL code that was running that caused the deadlock. 4. The oval symbols represent the process nodes a) Server Process Id : If you are using SQL Server Management Studio you can see the server process id on information bar at the bottom. b) Deadlock Priority : If you have not set DEADLOCK PRIORITY explicitly using SET DEADLOCK PRIORITY statement, then both the processes should have the same default deadlock priority NORMAL (0). c) Log Used : The transaction log space used. If a transaction has used a lot of log space then the cost to roll it back is also more. So the transaction that has used the least log space is killed and rolled back. 5. The rectangles represent the resource nodes. a) HoBt ID : Heap Or Binary Tree ID. Using this ID query sys.partitions view to find the database objects involved in the deadlock. SELECT object_name([object_id]) FROM sys.partitions WHERE hobt_id = 72057594041663488 6. The arrows represent types of locks each process has on each resource node.
Views: 42642 kudvenkat
How To Declare and Initialize Variables in T-SQL (Part 1)
 
10:31
This video is part of LearnItFirst's Transact-SQL Programming: SQL Server 2008/R2 course. More information on this video and course is available here: http://www.learnitfirst.com/Course161 In order to best demonstrate the differences between the data types, Scott first shows you how to create variables. What is a variable, and what are the differences between scalar and table variables? Is a variable declared in one session available for access in other sessions? What is a good rule of thumb for deciding whether to use SELECT or SET for variable assignment? Scott clarifies all of that and more in this video. Highlights from this video: - Global variable vs. local variable - Understanding "scope" - How the batch terminator affects variables - Using SELECT and SET for variable assignment - Declaring multiple variables and much more...
Views: 34022 LearnItFirst.com

Atacand plus 8/12.5 mg
When will Viagra be generic
Bula do alektos 20 mg cialis
Upload files with html forms and pure aspirin
Fluoxetine hcl caps 20mg cost