This is 46th episode of the memory lane. Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. My favorite article is about Scrum and SQL Reboot.
2007
User Defined Function – Get Number of Days in Month
A straight to script blog post where I find the number of days in month.
Start Stop Restart SQL Server From Command Prompt
Very frequently I use following command prompt script to start and stop a default instance of SQL Server. Our network admin loves these commands as this is very easy.
Frequency of SQL Server Reboot and Restart
This is a very interesting question. I will keep the answer of this question very simple. First of all there is no scientific research or white paper I can backup my results with. The answer contains part simple observation and part experience. There is no need to reboot SQL Server. Once it is on it is ON!
Scrum: Agile Software Development for Project Management
Scrum is organized around the following roles:
- Product Owner – Determines what functionality is needed
- ScrumMaster – Leads the Scrum and is primarily responsible for making sure the Scrum process is followed and removing impediments that keep the Team from working
- The Team – Those who do the actual work that translates what the Product Owner has requested into usable functionality
Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always
Using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.
2008
Creating Primary Key, Foreign Key and Default Constraint
Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be considered while creating tables or even after that. It seems very easy to apply these constraints but still we have some confusions and problems while implementing it. So I tried to write about these constraints that can be created or added at different levels and in different ways or methods.
SharePoint Stop Working After Changing Server (Computer) Name
When you change the physical server name the SharePoint is already connected to the SQL instance of the old computer name (OldServerName/SQLInstance) and on changing the name the SharePoint will not able to connect the SQL Server as now the SQL Server instance will run on new computer name (NewServerName/SQLInstance).
Steps To Create A Custom WebPart – Deploy It SharePoint Site
SharePoint does not allow custom coding for any of the webpart. It is possible to create a webpart in Visual Studio and integrate it with SharePoint. The process to create a webpart in .NET framework and make it work in SharePoint often fails due to lack of guidance on this subject on the internet.
2009
Difference between SQL Server Express and MySQL
Both SQL Server express and MySQL are two of the Relational Database Systems (RDBMS) available today. Both are freely available, and meant for running smaller or embedded databases, yet there are also significant differences between them.
Plan Caching and Schema Change – An Interesting Observation
As per the white paper, “Schema change” is defined as follows:
- Adding or dropping columns to a table or view.
- Adding or dropping constraints, defaults, or rules to/from a table.
- Adding an index to a table or an indexed view.
- Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question).
- Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table. Such recompilations occur at that instant when the query plan in question begins execution. Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that uses this statistic.
2010
Find Row Count in Table – Find Largest Table in Database – Part 2
Last Year I wrote articles on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.
Find Automatically Created Statistics – T-SQL
Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same organization.
What are Wait Types, Wait Stats and its Importance
As per BOL, there are three types of wait types, namely:
- Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
- Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
- External Waits. External waits occur when an SQL Server worker is waiting for an external event.
To check the wait types for any server, just run the following simple statistics:
SELECT *
FROM sys.dm_os_wait_stats
You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.
Disabled Index and Index Levels and B-Tree
This blog post tries to answer a very important question – What will be the status of the B-Tree structure when the index is disabled?
In the following script, the following operations are to be done:
- Create Table
- Create Clustered Index
- Check the Index Levels
- Disable Index
- Check the Index Levels
2011
I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.
| SQLAuthority.com | Book On-Line |
| Conversion functions | |
| PARSE | PARSE |
| TRY_CONVERT | TRY_CONVERT |
| TRY_PARSE | TRY_PARSE |
| Date and time functions | |
| DATEFROMPARTS | DATEFROMPARTS |
| DATETIME2FROMPARTS | DATETIME2FROMPARTS |
| DATETIMEFROMPARTS | DATETIMEFROMPARTS |
| DATETIMEOFFSETFROMPARTS | DATETIMEOFFSETFROMPARTS |
| EOMONTH | EOMONTH |
| SMALLDATETIMEFROMPARTS | SMALLDATETIMEFROMPARTS |
| TIMEFROMPARTS | TIMEFROMPARTS |
| Logical functions | |
| CHOOSE | CHOOSE |
| IIF | IIF |
| String functions | |
| CONCAT | CONCAT |
| FORMAT | FORMAT |
I have personally bookmarked this post for my future reference.
2012
Unable to DELETE Project in Data Quality Projects (DQS)
Here is the problem. I am not able to delete the project which I have created earlier. I am able to open it and play with it but the delete option is disabled and grayed out (see attached image). Now I believe there is nothing wrong with this project as it was just a test project. Would you please write to my manager that it is not harmful to leave that project there as it is? It is also not using any resources. I think he will believe you.”
Configuring Interactive Cleansing Suggestion Min Score for Suggestions in Data Quality Services (DQS) – Sensitivity of Suggestion
Would you please tell me how to increase the numbers of suggestion? I do understand this may not be preferable solution in many case but all the business cases go on it depends. There are cases when the high sensitivity required and there are cases when higher sensitivities are not required. I would like to seek your help here.
Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS)
In a database, these sorts of anomalies are incredibly important. Databases are often used by multiple people who rely on this data to be true and accurate, so data quality is key. That is why the improved SQL Server features Master Data Management talks about Data Quality Services. This service has the ability to recognize and flag anomalies like out of range numbers and similarities between data. This allows a human brain with its pattern recognition abilities to double-check and ensure that P. Dave is the same as Pinal Dave.
Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)
Let me paint a picture of everyday life for you. Let’s say you and your wife both have address books for your groups of friends. There is definitely overlap between them, so that you both have the addresses for your mutual friends, and there are addresses that only you know, and some only she knows. They also might be organized differently. You might list your friend under “J” for “Joe” or even under “W” for “Work,” while she might list him under “S” for “Joe Smith” or under your name because he is your friend. If you happened to trade, neither of you would be able to find anything!
Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video
Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System
The goal of this article is to answer following simple questions
- What is Elasticity?
- What is Scalability?
- How ACID properties vary from NOSQL Concepts?
- What are the prevailing problems in the current database system architectures?
Grouping by Multiple Columns to Single Column as A String
One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.
Reference: Pinal Dave (https://blog.sqlauthority.com)
