![BeardedDev](/img/default-banner.jpg)
- 121
- 1 135 955
BeardedDev
Registrace 27. 12. 2016
Hi, I am BeardedDev, a Data Engineer specialising in Microsoft technologies with a true passion for data and learning. I have experience across a variety of sectors and enjoy helping people develop and solve problems. On my channel I create tutorials focusing on T-SQL, Data Engineering, Data Analysis and Business Intelligence across SQL Server; SSIS, SSRS and SSAS and Azure; Azure SQL Database, Azure Data Factory, Azure Data Lake Storage, Azure Synapse Analytics and Azure Databricks.
SQL Tutorial - How to search text in a Stored Procedure
Learn how to search text in a stored procedure, if you want to know what stored procedures are inserting, updating or deleting data in a table this tutorial will show you how. Using a combination of system catalog views, sys.objects and sys.sql_modules you will not only be able to find all stored procedures that reference a table but other objects too such as views, triggers and functions.
Further reading can be found on Microsoft Docs:
sys.objects
docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver16
sys.sql_modules
docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-ver16
Further reading can be found on Microsoft Docs:
sys.objects
docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver16
sys.sql_modules
docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-ver16
zhlédnutí: 4 655
Video
SQL Tutorial - How to change data in a View
zhlédnutí 2,3KPřed 2 lety
Learn how to change data in a View in SQL, in this SQL Tutorial I will be talking about how to INSERT, UPDATE and DELETE data in Views. An important point to remember when modifying data in a View is that no changes are actually happening to the View, instead the data is being changed in the underlying base table. I talk through the rules for updateable Views and test out different scenarios.
SQL Tutorial - How to compare multiple columns in SQL
zhlédnutí 10KPřed 2 lety
Learn how to compare multiple columns in SQL, in this video I cover comparing multiple columns in the WHERE clause, using CHECKSUM, BINARY_CHECKSUM and HASHBYTES. I also discuss the advantages and disadvantages of using CHECKSUM, BINARY_CHECKSUM and HASHBYTES and what is my preferred approach for comparing multiple columns. An important point when using CHECKSUM, BINARY_CHECKSUM or HASHBYTES to...
SQL Tutorial - How to update data using joins in SQL
zhlédnutí 4,6KPřed 2 lety
Learn how to update data using joins in SQL and much more, in this video we will cover how to perform updates using joins in SQL, how to update multiple columns at the same time, how to update data using CTEs and how to update data within a transaction, we will also cover what sanity checks need to carried when out when performing updates. #beardeddev #sql #data #dataengineering #dataanalysis T...
How to Debug Data Flows in Azure Data Factory
zhlédnutí 2,3KPřed 2 lety
Learn how to debug data flows, how to turn on data flow debug, how to configure data flow debug settings and how much data flow debug costs. In this video we look at a data flow created previously and how we can see how the data changes with each transformation, we also discuss how much data flow debug costs, unfortunately it's not free and more details can be found on the link below, keep an e...
How to transform data using Data Flows
zhlédnutí 1,8KPřed 2 lety
Learn how to transform data using Data Flows within Azure Data Factory. Data Flows are an no code/low code solution that allow us to connect to data sources, transform data then write that data to a data destination. In this video we cover what transformations are available within Data Flows, work through an example where we pick up a file, apply an aggregate transformation followed by a derive...
SQL Tutorial - Stored Procedures Optional Parameters
zhlédnutí 2,4KPřed 2 lety
Learn how to create stored procedures with optional parameters in under 10 minutes. In this video I walk through a simple stored procedure example where I assign default values for parameters within the stored procedure definition, this prevents the need of adding all parameters every time you execute the stored procedure. Assigning default values within the stored procedure definition effectiv...
SQL Tutorial - How to drop a column from a table
zhlédnutí 763Před 2 lety
Learn how to drop columns from a table in this video tutorial and also why you might come across some errors. In this SQL Tutorial I cover: How to drop columns from a table How to drop multiple columns from a table What impact does dropping columns have on Views that include those columns How to drop computed columns and what happens if you try to drop a column that is part of the computation W...
SQL Tutorial - How to create a Dynamic Pivot in SQL Part 2
zhlédnutí 3,4KPřed 2 lety
In this tutorial I demonstrate how to create a dynamic pivot query in SQL using STRING_AGG, a function added in SQL Server 2017. Performing pivot operations in SQL can be quite static when we have to list out each attribute and building a dynamic pivot query can help us solve that problem. Dynamic pivot queries also have the added benefit that we don't need to change the query when new values a...
SQL Tutorial - How to add total rows and columns to a Pivot Query
zhlédnutí 7KPřed 2 lety
Learn how to add total rows and columns to a pivot query in 15 minutes. In this SQL tutorial I show you how to add totals to pivot queries, I explain how to add a total row and and a total column. Adding totals to pivot queries in SQL Server Management studio is not my preferred approach but it can be done and it can be as simple as adding rows together. In the video I discuss the different app...
SQL Tutorial - How to create a Dynamic Pivot in SQL Part 1
zhlédnutí 7KPřed 2 lety
In this tutorial I demonstrate how to create a dynamic pivot query in SQL, performing pivot operations in SQL can be quite static when we have to list out each attribute and building a dynamic pivot query can help us solve that problem. Dynamic pivot queries also have the added benefit that we don't need to change the query when new values are added like we would have to do with a static pivot ...
SQL Tutorial - Nesting CASE statements
zhlédnutí 20KPřed 2 lety
Learn how to nest CASE statements in this SQL tutorial, nesting CASE statements can be used for layers of conditional logic but can become complex and difficult to read so always remember to add comments to your code, I also like to indent my case statements to improve readability. The other issue with nested CASE statements is that they are not very dynamic but we could overcome that problem b...
Data Factory Tutorial - How to add an Pre Copy Script
zhlédnutí 6KPřed 2 lety
Learn how to add an Pre-copy script to your pipelines in this Data Factory Tutorial. In this video I show you how to truncate a table using an Pre-copy script within a copy activity. This is useful if you are loading data to a staging table that will then be processed further, we don't want to just keep adding data and a Pre-copy script can be used to truncate the sink or target table. I also d...
SQL Tutorial - How to Join to Derived Tables
zhlédnutí 2,6KPřed 2 lety
In this SQL Tutorial learn to how to join to derived tables. I talk you through the benefits of derived tables, show you to create a derived table then how to join to the derived table. In the video we work through practical examples, creating the same query with a join between two tables then grouping and creating a derived table then joining to give the same results. If you are not familiar w...
SQL Tutorial - Difference between CTEs and Derived Tables
zhlédnutí 2,7KPřed 2 lety
In this SQL Tutorial I talk through the differences between CTEs and Derived Tables in SQL Server. CTEs actually came along to address some of the limitations of Derived Tables, when nesting Derived Tables it can be difficult to understand what the query is doing, CTEs address this by allowing you to read the SQL code from top to bottom, as Derived Tables are also declared in the FROM clause of...
Data Factory Tutorial - How to create your first pipeline
zhlédnutí 7KPřed 2 lety
Data Factory Tutorial - How to create your first pipeline
Power BI Tutorial - How to Unpivot data in Power Query
zhlédnutí 7KPřed 2 lety
Power BI Tutorial - How to Unpivot data in Power Query
T-SQL Tutorial - Aggregate Window Functions Performance
zhlédnutí 1,5KPřed 2 lety
T-SQL Tutorial - Aggregate Window Functions Performance
T-SQL Tutorial - When is DISTINCT evaluated
zhlédnutí 748Před 2 lety
T-SQL Tutorial - When is DISTINCT evaluated
T-SQL Tutorial - How to create a custom sort order
zhlédnutí 1,7KPřed 2 lety
T-SQL Tutorial - How to create a custom sort order
How I passed Azure Fundamentals (AZ-900)
zhlédnutí 579Před 3 lety
How I passed Azure Fundamentals (AZ-900)
How to create Derived Tables in SQL Server
zhlédnutí 8KPřed 3 lety
How to create Derived Tables in SQL Server
SQL Tutorial - How to create Views with Schemabinding
zhlédnutí 3,1KPřed 3 lety
SQL Tutorial - How to create Views with Schemabinding
SQL Basics - Querying (learn how to write queries in 30 minutes)
zhlédnutí 3,2KPřed 3 lety
SQL Basics - Querying (learn how to write queries in 30 minutes)
How I became an Microsoft Certified Solutions Expert in Data Management and Analytics
zhlédnutí 729Před 3 lety
How I became an Microsoft Certified Solutions Expert in Data Management and Analytics
Introduction to Statistics in SQL Server
zhlédnutí 4KPřed 3 lety
Introduction to Statistics in SQL Server
SQL Tutorial - Subqueries (Scalar Valued)
zhlédnutí 3KPřed 3 lety
SQL Tutorial - Subqueries (Scalar Valued)
Not much use if you need to know the columns. you surely need to get distinct values before you pivot. so its not dynamic
Appreciate that, that's why I have separate videos on dynamic pivoting. You will find though even writing a basic query you will need to know the data.
It wasnt a comment about your video. it was a comment about pivot itself..... thank you for the reply
Hi BeardedDev, Many thanks for this video. It's my first one, I'm only starting with Azure multiverse - via your videos. I see some difference in the functions available nowadays, I guess they have changed in the last couple of years... Most of it is not so important, of course. However, I did get desperately stuck at the step of authentication when connecting to SQL server on my SSMS [Error 18456]. After trying all possible trouble-shooting hints all over the internet, I (purely accidentally) discovered that my authentication method shuld be 'Entra MFI'. I was logged into my Azure account with my usual Microsoft account credentials...probably for that reason [how bad is this idea, btw?]. As far, as I understand, this whole Entra thing is new and slightly differs from the previous options. This might be worse some update on this video in the future or might be just useful info for some other viewers. 🙃 Once I overcame my authentication challenge, the rest went very smoothely - cause your video is really good! Thank you!!!
Hi, a lot does change now at rapid pace in the cloud, UIs can change quite frequently. Entra used to be Active Directory, that's always best practice, the reason I use SQL Server Authentication which is still supported is because I'm working on a personal subscription, I don't have Entra setup but great call its something I should call out. Great work on starting with Azure, it's lots of fun.
Can Null be removed from the output value. i have currently hardcoded IsNull along with cols name. Can we make dynamic query including IsNull??
I think it could work, you can't remove NULLs though, only replace.
Wow, thank you so much!
You're welcome!
This is much better than information_schema
This was easy to understand. Thank you.
TY! Solved my problem. well done.
Excellent explaination and presentation! Thank you BeardedDev!!
Wao just wao it's super simple easy thanks man.
Thank you
I really appreciate the amount of effort you put into this tutorial. Your hard work is not lost on me. Your explanation are clear and examples are easy to follow along with. Thanks again and God bless🙏🏽🙏🏽
Thanks so much.
Finally, I understood the window functions easily...Thanks alot
Glad I could help.
This solution breaks down if there are not any sales for a day: it would grab sales from a neighboring day instead of counting the missing day as zero. How would you normalize the time series data to account for that?
The solution here is about running totals or averages rather than replacing values. Let's say we start our data on 01/01/2024 and sales were 0, on 02/01/2024 sales were 100. If we have a running total on 02/01/2024 it would be 100 taking into account the previous days sales of 0 and the current days of 100. We might want to do this if say we work for a company that has peaks of sales on weekends, we might want a rolling total of the last 7 days rather than peaks every weekend. All we have really done is change the scope of the dates considered in that particular column, this is easily communicated through column names such as rolling7daytotal. We can also add a description if we use a tool to output the data that allows us to, hopefully that helps.
If my table contains customer details having birthdate with other information Then my requirement is that I need birth date wise data (consider only 1 month in this case) with birthdate count column as Total Like Below Column name :--- '2013-03-01' having value 4 (4 is count of customer w.r.t. birthdate) '2013-03-02' having value 3 '2013-03-03' having value 5 Remaining column name :- '2013-03-04' to '2013-03-31' having value 0 or null Last column name :- birthdate_count=3 having count as per above (because three columns have value greater than 0 and remaining have 0 value)
This sounds more of a simple aggregation, first you'd need to manipulate the birthdate to get a consistent date for each month then group by it with a count.
Hi
@@BeardedDevData CREATE TABLE yourtable ([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime) ; INSERT INTO yourtable ([role], [familyname], [givenname], [skill], [level], [id], [date]) VALUES ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'), ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00') ; select date, (case when date is null then count(distinct date) else count(*) end ) daywisetotal from yourtable where date between '2013-03-01' and '2013-03-31' group by cube (date) having count(*)>0 ; => Output (this is the output what I required) date daywisetotal ----------------------- ------------ 2013-03-01 00:00:00.000 1 2013-03-07 00:00:00.000 1 2013-03-27 00:00:00.000 2 NULL 3 Need same output in pivot Like Total '2013-03-01' '2013-03-07' '2013-03-27' Row1 :- 3 1 1 2 But I am getting output in more than 1 row . Can please help me for getting same output in pivot ?
CREATE TABLE yourtable ([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime) ; INSERT INTO yourtable ([role], [familyname], [givenname], [skill], [level], [id], [date]) VALUES ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'), ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00') ; select date, (case when date is null then count(distinct date) else count(*) end ) daywisetotal from yourtable where date between '2013-03-01' and '2013-03-31' group by cube (date) having count(*)>0 ; => Output (this is the output what I required) date daywisetotal ----------------------- ------------ 2013-03-01 00:00:00.000 1 2013-03-07 00:00:00.000 1 2013-03-27 00:00:00.000 2 NULL 3 Need same output in pivot Like Total '2013-03-01' '2013-03-07' '2013-03-27' Row1 :- 3 1 1 2 But I am getting output in more than 1 row . Can please help me for getting same output in pivot ?
I need some help if in your example instead of product if its date (like monthly date '2012-03-01', '2012-03-02','2012-03-03','2012-03-04', so on.. upto 2012-03-31' ) and instead of customer total column i required monthly day count with respect to day value means if march month having total 31 day in that 25 day having value greater than 0 and remaining 6 day have null or 0 value then monthly day count should be 25 So please help in this case
It would depend on what you want the output to look like, it sounds like you'd need a SUM(CASE ... as a starting point.
Very well explained ❤❤
love it thanks
Ditto all that. Truly the best format so a person can understand the terms as well as what is happening. As he builds the pivot table when he writes over the <words that define the functionality> you can understand what is happening. He uses terms like the spreader. Describing what is going to spread out or fan out the columns of data that will be displayed. Good job .....really good job.
Thanks so much.
I'm starting to think all the Microsoft documentation is written by AI xD. It's so overly complicated.
Thank you BD
Thanks helped a lot
Glad I could help.
Possibly the best explanation on the internet. Thank you.
Thanks so much.
Thank you!
No problem.
Perfect explanation
Thanks so much.
Thank you for such a thorough explanation
Thank you for watching
Understandable, clear, and concise. Thank you!
Thanks a lot for this, I got it right on time.
Hi there, I’m new to ssis and connection manager- provider shows “Native OLE DB/SQL Server Native Client RDA 11.0” and giving me failure. If you could do a video about it that would be great.
Could you please share the dataset to practice along. Thanks !!
Hey, that video and the explanation is absolutely great! Thanks a lot! Although everything works just fine I don’t understand why do we create the @query variable as a string :?
We need to use a valid data type for our parameter, in this case we are building the query, something of variable length that contains text. NVARCHAR(MAX) is overkill in this scenario.
from where to get xlxs file to try your instructions pls
can you share code pls
can you share code pls
It's in the description.
@@BeardedDevData thanks, do you have video for windows functions with code pls
Sure, I have done a follow along video: czcams.com/video/lBcDSsgp0RU/video.html
How to find column name in proc Example goodsvalue+taxamount billamount i find the billamount in proc give the output text=goodsvalue+taxamount Any possible?
You can search anything as long as its in the definition of the stored procedure but remember you are just searching text. Can just pass in the column name, can even search expressions such as goodsvalue+taxamount but this won't return any stored procedures where the expression is written as goodsvalue + taxamount. Hopefully that provides some clarity.
Thank you for your reply
Exec sp_helptext @object='procedure name',@columnname=billamount How use procedure?
I want to truncate the stage table only once, before the For loop start. how to write that in precopyscript?
I would recommend using a script activity.
how can we write a precopyscript for truncating the Stage table once before the For loop start.
I would recommend using a script activity.
Thank you
great tutorial I like your style. But the table you worked on is not attached so I am giving it 6/10, thumb down. Please upload the table. Like now the video is below modern standards because it's hard to follow what you talked about.
Appreciate that, the idea behind was that if I show you how to do something and you can apply it with your data then you've learnt something, if I give you the data and you follow what I'm doing all you've done is follow a tutorial. I understand people might like to test things out first though so will take that on board.
why do we have use sum here , without using the sum logic seems correct to me
I understand where you are coming from but because we are grouping data we need to use an aggregate function otherwise the query would fail.
Great video, thank you. Would you consider implementing the various checksum methods for data migration of millions or hundreds of millions records?
It would depend on your acceptable range of collisions, certainly checksum is out the window, you'd have to use hashbytes with an algorithm that generates at least a 64-bit value. I've just had a look online for some numbers, found some details that say at 609 million rows you have a 1 in 100 chance of a collision, that is different inputs returning the same value, this may be acceptable as the alternative is column by column comparison to get 100% results. If this is a one off activity you could also use hashing then handle any collisions separately.
I have tried so many things in excel to get data to look like this, so i could load it into power bi, and with small amount of data excel can do the trick, but my man you just saved my ass at work. Thank you so much for this. :)
Fantastic, glad I could help.
Hey Brad! Thank you for this. My code works, but now I want to save the result as a view. how do i do that?
Unfortunately you wouldn't be able to run this code within a View because it's a dynamic query, you need to use a Stored Procedure.
Excellent Brad🤩
Hi BeardedDev, I do have a challenge that I am facing. Let say you are creating a view on top of multiple schemas dbo and dbo2, dbo2 has some auxiliary fields in new table, that in the future will become a single schema in dbo. How would you change data in that sort of view where insert is needed for both tables? Lets say I don't want to use triggers on the view and schema dbo cannot be changed. Will really be interested to know your answer. Thanks.
Think we should throw updating through a View out of the window for that scenario, this video was to show it can be done but it's very rare that it should be done. Creating the View on top of multiple tables is great but to run multiple inserts especially if the tables are related you need to use a stored procedure to control logic and add error handling.
Can you think of a scenario where you might use Unpivot Only Selected Columns in Power BI? I thought about it a lot, but I cannot come up with a real life scenario where this would be helpul. Anyway, you did a great job in explaining the differences. Thank you!
That's a really good question, I think there can be some scenarios but it all depends on what can happen to the underlying data, in the example use case I had somebody could add a subcategory to the file. Another case could be where we have a file per year and months as the headers, we would only want to unpivot the months in this case.
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
You can create a window function in views but to see the table and columns being used you'd have to look at the definition of the view
That was really informative! I wonder how this gem was hidden for so many years!
how to truncate multiple tables, if i use multiple truncate statetements it is copy only one and the rest are left with 0 records
The first thing you need to consider is do you want to truncate all tables or one at a time, this is important to consider because if you get any failures you will be left with blank tables, there are a couple of approaches to copying multiple datasets, the first is multiple copy activities, these can either be dependent in a chain or ran in parallel, the second option is metadata driven Pipelines where you can store the tables to copy within a table then call that table and look through.
Why we need to give columns in Group by clause which are using case statements? Select c1, c2='x' sum(values in c3) sum(values in c4) end as 'z' from table A where c5='Y' and c6='w' group by c1, ( Do we need to use c2 here ) c1, Z column (I need Z values grouped by only c1 column )
I think you're asking why we need to use the Customers table, that's because the discount depends on the CustomerType. If you can clarify the question a bit more, I can help.
Very clear and well stated explanation.
What is difference b/w set operaters and joins interms of output
Good question, with set operators they are used for a comparison between sets or to combine sets, as an example, we might have a table representing employees and another representing customers, we might want a set of employees who are also customers. In terms of joins, we need more information than we have in the existing table, following on from the previous example, we may want to left join customers to employees to see when an employee is a customer what their employee number is. In terms of set operators, we aren't adding any more columns but are adding rows if combining, in terms of joins we are typically adding more columns and maybe more rows depending on the data we join. Hope that helps.