About
I am an aspiring Data Engineer; focused primarily on Microsoft technologies…
Articles by Dwaine
-
A Late, but Important Request in the Memory of Gareth Swanepoel
A Late, but Important Request in the Memory of Gareth Swanepoel
By Dwaine Wright
Contributions
-
How can you optimize SQL queries for NULL values?
The decision should be between (NOT) EXISTS and a JOIN/SEMI JOIN approach. The performance and quirks of NOT IN make it a losing proposition in my experience. Be careful and test when deciding between EXISTS and a JOIN, as the nature of the two sets (one large, the other a smaller subset versus two large unrelated sets, etc.), constraints (NOT NULL, FK, etc.), and indexes can drastically change the plan (and thus IO and CPU) used in different situations. Please forget about IN/NOT IN.
-
How can you optimize SQL queries for NULL values?
Although their performance is similar, ISNULL is a T-SQL extension and thus less portable than COALESCE. Plus, down the road, if you need to compare yet another expression, COALESCE can take more than the limited two arguments allowed in ISNULL. Minimal code change is required! And don't use a hammer to drive a screw! Several other options may be more appropriate to your situation. Learn the different functions that may better serve your purpose, like MIN/MAX, FIRST_VALUE/LAST_VALUE, and GREATEST/LEAST, leveraging the OVER clause if appropriate.
-
How can you optimize SQL queries for NULL values?
If your table has many NULL values, you can also test the efficacy of creating a filtered index by adding WHERE NullableColumn IS NOT NULL. That will keep the index smaller. But you'll have to test whether the index is actually used in the wild. SQL Server can be finicky about using/ignoring filtered indexes.
-
How do you design and implement table-valued parameters in stored procedures for complex scenarios?
If your environment supports in-memory (Hk or Hekaton), there's a game-changing optimization that can be achieved by creating the table type in this manner: CREATE TYPE dbo.typeTableD AS TABLE ( Column1 INT NOT NULL INDEX ix1, Column2 CHAR(10) ) WITH (MEMORY_OPTIMIZED = ON); This can also be used to replace global and local temporary tables! More details can be found at https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver16#d2-convert-explicit-on-disk-to-memory-optimized
Activity
-
Deeply honored to receive the Frederick J Carleton Award at the PDA Annual Meeting in Long beach. Thanks to all my colleagues at PDA for so many…
Deeply honored to receive the Frederick J Carleton Award at the PDA Annual Meeting in Long beach. Thanks to all my colleagues at PDA for so many…
Liked by Dwaine Wright
-
I'd like to ask anyone interested in SQL Server development to view my suggestion and upvote if you agree. "Please allow specifying NOT NULL on…
I'd like to ask anyone interested in SQL Server development to view my suggestion and upvote if you agree. "Please allow specifying NOT NULL on…
Shared by Dwaine Wright
-
If you have any interest in anything Azure, you should be familiar with charts. Period.
If you have any interest in anything Azure, you should be familiar with charts. Period.
Shared by Dwaine Wright
Experience
Education
Licenses & Certifications
Publications
Test Scores
-
70-461 Querying Microsoft SQL Server 2012
Score: 906
Organizations
-
Association for Computing Machinery (ACM)
Member
- Present -
PASS
-
- Present
Recommendations received
8 people have recommended Dwaine
Join now to viewMore activity by Dwaine
-
What Does a Good DBA Team Look Like? https://lnkd.in/gMGzsU6S
What Does a Good DBA Team Look Like? https://lnkd.in/gMGzsU6S
Liked by Dwaine Wright
-
Many of you that know me noticed an uptick in activity on my end. This is both professional and personally. Personally, I have started BeSelfEvident,…
Many of you that know me noticed an uptick in activity on my end. This is both professional and personally. Personally, I have started BeSelfEvident,…
Liked by Dwaine Wright
Other similar profiles
Explore collaborative articles
We’re unlocking community knowledge in a new way. Experts add insights directly into each article, started with the help of AI.
Explore MoreOthers named Dwaine Wright in United States
-
Dwaine Wright
Overgoods Team Leader at United Parcel Service
-
Dwaine Wright
GS12 at US Government
-
Dwaine Wright
--
-
Dwaine Wright
--
8 others named Dwaine Wright in United States are on LinkedIn
See others named Dwaine Wright