Welcoem to posts!!
in the future - u will be able to do some more stuff here,,,!! like pat catgirl- i mean um yeah... for now u can only see others's posts :c
Did you know that indexes come with their own set of challenges? Let's explore three significant drawbacks associated with indexes:
1. Slow-Running Insert and Updates: Indexes can lead to slower data insertion and update operations, impacting the efficiency of your database.
2. Slow-Running Select Statements: Indexes aren't a silver bullet; they can sometimes reduce the performance of select queries. Learn more about this in this informative article provided by Pinal Dave (blog.sqlauthority.com/2019/05/28/sql-server-an-ind….
3. Table Locking During Index CREATE/ALTER: When creating or altering indexes, your tables can become locked, potentially causing disruptions. Discover how to avoid table locking in this insightful article from Pinal Dave (blog.sqlauthority.com/2019/11/16/sql-server-create….
Do you know of any other disadvantages associated with indexes? Let's discuss it!
#sql #data #index #dataengineer #datascience
6 - 0
NULL is the Chuck Norris of the database
.
.
.
.
.
nothing can be compared to it.
#sql #data #joke #dataengineering #datascience
3 - 2
19 - 0
So, I had this interesting experience recently.
I was working on updating an old Stored Procedure, and I ran into a bit of a SQL head-scratcher. Ever seen the 'Cannot resolve the collation conflict between … and … in the equal to operation' error? It's a real puzzle, especially when you're dealing with a super-long code, and all it gives you is the starting line number.
The issue here is that SQL Server doesn't automatically handle collation conflicts when comparing columns with different collations. But hey, there's a nifty trick for that – you can use COLLATE casting at the query level, like this:
Col1 = col2 COLLATE Latin1_General_CI_AS
It saved me a ton of time and made that Stored Procedure run like a dream, but it’s good to know that COLLATE casting at the query level can cause some query performance issues.
What was your SQL head-scratcher ERROR?
#sql #error #dataengineering #dataandanalytics #datasciencecommunity #data
5 - 0
Now seriously, what would you do?
#sql #data #dataengineering #update #dataanalytics #datascience
6 - 6
Yesterday, my 5-year-old daughter was watching the 'Bluey' cartoon (The Adventures of an Australian Blue Heeler Cattle Dog puppy named Bluey with her family and friends). In one episode, Bluey and his buddies changed the rules of a game to make it easier. It got me thinking about some parallels in our work lives.
How often do we see colleagues take shortcuts, like using 'SELECT *' or skipping QA? It may seem convenient at the time, but the lesson from the cartoon stuck with me: "That's why you can't change the rules because the rules make it fun!"
So, here's a gentle reminder to my fellow data professionals: Let's stick to best practices. Avoid those quick fixes, follow coding standards, and respect QA processes. By doing so, we not only ensure smoother operations but also create a more enjoyable and efficient work environment for all of us.
#DataProfessionals #BestPractices #CodingStandards #sql #QA #WorkEnvironment #Efficiency #ProfessionalDevelopment #bluey
2 - 0
We all transform our data types in SQL, IMPLICITLY or EXPLICITLY but do you know when and how to do it correctly?
Here are some tips that will help you when converting data🔥
✅IMPLICIT CONVERSIONS are not visible to the user. SQL Server automatically converts the data from one data type to another, when data from one object is moved to, compared with, or combined with data from another object. For example, when a SMALLINT is compared to an INT, the SMALLINT is implicitly converted to INT before the comparison proceeds.
When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. If the conversion isn't a supported implicit conversion, an error is returned. For an operator combining operand expressions having the same data type, the result of the operation has that data type.
SQL Server uses the following precedence order for data types:
1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)
✅EXPLICIT CONVERSIONS use the CAST or CONVERT functions.
The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another.
🔥TIP🔥: Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types.
#sql #cast #convert #datatypes #dataengineer #dataanalytics #dataengineering #sqlserver
8 - 0
Have you ever heard about ANSI-89 and ANSI-92 SQL Standard?
At every company that I have worked at, I have always found some SQL queries in the ANSI-89 standard, which look something like this:
SELECT a.id, b.id, b.address_1, c.country
FROM person a, address b, countries c
WHERE a.id = b.id AND c.id = b.country_id AND a.Name = ‘Anderson’
rather than the ANSI-92 standard:
SELECT a.id, b.id, b.address_1, c.country
FROM person a
INNER JOIN address b
ON a.id = b.id
INNER JOIN countries c
ON b.country_id = c.id
WHERE a.Name = ‘Anderson’
For a simple query like the one above, there's not a big difference in readability, but for large queries (bigger number of JOINS), I find that having the JOIN criteria grouped together with the joined TABLES makes it much easier to read and then at the end keep only the filtering in the WHERE clause.
PS: There is no difference in the performance of SQL-89 versus SQL-92 style joins.
Which of the standards do you use?
#sql #data #dataengineer #dataanalytics #dataengineering #sqlserver
3 - 0
We are now 3000 Heroes🔥👏! Thank you all for your support, we will achieve beautiful things togheter 🤗📉📈!
#data #sql
3 - 0
“The goal is to turn data into information, and information into insight.” – Carly Fiorina, former executive, president, and chair of Hewlett-Packard Co.
Made to share knowledge and tricks about SQL SERVER.
Let's become a beautiful community!
Be a hero!
#sql #sqlserver #database