Data Tips & Tricks

Useful script to rebuild table indexes and update stats You can update the queries based on your requirement. To update the table stats --Query to update the table stats SELECT N'UPDATE STATISTICS ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' ;' FROM INFORMATION_SCHEMA.tables AS t WHERE t.TABLE_TYPE = N'BASE table'; GO To re-build table indexes: --To rebuild table index DECLARE @myDatabaseName NVARCHAR(255) = N'your-database-name'; DECLARE @runningTableName NVARCHAR(255); DECLARE @cmd NVARCHAR(1000); DECLARE @name NVARCHAR(100)= 'your-database-name'; -- Populate the database cursor. DECLARE myDatabaseLevelCursor CURSOR READ_ONLY FOR SELECT @name; OPEN myDatabaseLevelCursor; FETCH NEXT FROM myDatabaseLevelCursor INTO @myDatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN -- In this command we're declaring table level cursor SET @cmd = 'DECLARE myTableCursor CURSOR READ_ONLY FOR SELECT '...

Sometimes in the large project when we separate it in different repositories we need to take reference of one repository in another where some the sub-module concepts. Now if we move our submodule to a different repository or renaming its repo-path then we've to also update the repository where this sub-module is getting referenced. Let's see how to update the git submodule.  Step-1: First clone the repo Step-2: Open git UI to create a new local branch from master  Step-3: Open git bash -- Now run below command to (remove submodules & add them):  -- Remove submodule git submodule deinit YOUR_FIRST_REPO git rm YOUR_FIRST_REPO git commit -m "Removed submodule YOUR_FIRST_REPO" rm -rf .git/modules/YOUR_FIRST_REPO -- Add submodule git submodule add <<..YOUR_FIRST_REPO git URL>> Step-4: All good, let's push our changes to the master

While working with Azure SQL using SQL server management studio (SSMS), most of the feature which we used to get through SSMS GUI while connecting with OnPre/IaaS SQL-Server, is not available (e.g.: get the user's role assignment, create user/role ..) Here are a few useful T-SQL queries: To create a role Create Role IF DATABASE_PRINCIPAL_ID('<<your-role-name>>') IS NULL BEGIN -- Add Role here CREATE ROLE [<<your-role-name>>]; END To create a user Create User if not exists [<<your-user-name>>] IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '<<your-user-name>>' ) BEGIN CREATE USER [< >] FROM EXTERNAL PROVIDER ; END Get user role & grant permission summary Role & grant permission summary: SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permiss...

Search in SQL server query is generally case insensitive (by default or based on database level collation). Suppose we have an employees table with a row having first-name column value as "My-First-Name", so if we want to do the case-sensitive search we have to explicitly use the related collate: In EF 5 (currently in Release Candidate version [RC.2.20475.6]) Collate function got introduced which helps us to use our specific collation based search.  C# with EF5 code sample: var employeeCaseSensitiveSearch = _dbContext.Employees .Where(x => EF.Functions.Collate(x.FirstName, "Latin1_General_CS_AS") == "my-first-name") .FirstOrDefault(); A related database query will be something like this: T-SQL: Case sensitive search (use specific collation e.g.: Latin1_General_CS_AS) SELECT * FROM dbo.Employees AS e WHERE e.FirstName Collate Latin1_General_CS_AS = 'my-first-name' Some of the useful CSharp function which g...

Why my update command using EFCore is not updating the underlying data in the database though in middle-tier code it's successful, not throwing any error? Let's try to get the root cause and how to solve this.  When creating the model class of related database object if the primary key name is Id then no need to explicitly mention the Key in ef-core OnModelCreating.  But if we've key name different from Id then we have to explicitly mention it in our OnModelCreating. Something like this:  entity.HasKey(c => new {c.CustId }); Assume in the database we've Customers table like this: CREATE TABLE [dbo].[Customers]( [CustId] [INT] IDENTITY(1,1) NOT NULL Primary Key, [FirstName] [VARCHAR](50) NULL ) Point to ponder: In the database table, column CustId is already defined as Primary key.  In EFCore model setup, it's up to us to configure the Key as (custId) or configure it as HasNoKey() Assume we've set our entity with Ha...

EFCore versions, we don't have GUI to manage the DB-entities & dbContext like we used to have with EF6 (or EF5/4 version). With EFCore we've the Scaffold-DbContext command which helps us to create entities & DB context from our DB-objects.  Let's see how to use Scaffold-DbContext: Assume we're connecting with SQL Server so after creating a sample project you'll have to install EFCore related libraries as mentioned below.  Install entity-framework-core for SQL Server, at this time 3.1.9 is the latest stable version   Microsoft.EntityFrameworkcore   Microsoft.EntityFrameworkCore.SqlServer  To use Scaffold-DbContext, following two NuGet packages are required Microsoft.EntityFrameworkCore.Tools  Microsoft.EntityFrameworkCore.Design Let's say we've two DB tables (e.g. dbo.Employees & dbo.Users )  Now let's see how to use Scaffold-DbContext to create c-...

Practically there are multiple instances when we want to do parallel DB call which will save us some time. Like if we want to get a few employees details as well as users detail and if both entities are independent of each other better to go with a parallel call to fetch the details.  With the growing ORM usage and enhanced & optimized framework of like EFCore, in most of the application (related to C# development), we generally prefer to use EFCore. With EFCore if we try to use the same DB context to do parallel db call we are getting an error (... different thread using the same DbContext....). Because dbContext call is not thread-safe Parallel DB call on the same dbContext:  Code snipped: which will throw an error private void DbCallWithParallelCallOnSameDbContext() { try { var employeesTask = _dbContext.Employees.ToListAsync(); var usersTask = _dbContext.Users.ToListAsync(); ...

 Is there a way to read Azure Active directory Group (Service or Microsoft 365) programmatically  There are different ways to do it one of them is using AAD App  let's see with the following example:  Pre-requisite: Create an AAD App  Get the AAD Group Member Reader & User Read All permission through Microsoft Graph (application permission)  Create a client secret (we'll use this to access your AAD Group information in the context of AAD App) Create a CSharp project (detail mentioned below) Create and AAD App with AAD GroupMember read & user read all permission  Step-1: Create an aad app: Login to azure porta => Azure Active Directory => App registration => create new app   Step-2. Once AAD App is created (e.g.: AADGroupReader)        Open it.    Go to it's API Permissions ...

 Azure Active Directory (AAD) Group can be helpful in multiple places to do authentication & authorization for Azure PaaS application. Currently, there are two types of AAD Group Service (group): Used to manage computer and user access for a group of users. It can have users, group, device or another service principal as it's member and users. It can only have a service principal as it's the owner.  Microsoft 365 (group): Used to give members access to shared email-box, files, calendar and more.. It can have only users as it's a member. It's can be users or service principal both.  Let's see how to create AAD group (e.g. Microsoft 365 group):  Step-1: Login to your Azure portal ( https://portal.azure.com/ )  and go to Active Directory Group Step-2: Select the group and click on Add new group Step-3: Fill the required entries in New group form .  Select the group type Enter the group name.  Enter the description.  Click on Create button Step-4...

While debugging the application (on a serious note) or doing code review sometimes if there is a string not-null or not-empty validation check it's obvious we may ignore those negate condition (!). for example  if (!string.IsNullOrWhiteSpace(inputString)) { // do someting here } To ignore these we generally adopt a rule  to instead checking with negate condition use the (== false)  for example if (string.IsNullOrWhiteSpace(inputString) == false) { // do someting here } Isn't it's better to use StringUtility methods with all the possible string validation check in one place and use them in multiple projects as-is.  Some of the frequently used utility methods. public static bool AreTrimEqual(string input1, string input2) { return string.Equals(input1?.Trim(), input2?.Trim(), StringComparison.InvariantCultureIgnoreCase); } public static string ToTitleCase(string title) { var culture...

Not getting SQL Server configuration manager in my windows search.  Though we've installed SQL Server & SSMS on my local system (or some other server) but on windows search not getting the "SQL server configuration manager" or "SQL Server 2019 Configuration manager". Even when we try to open the configuration manager console from its default location I'm getting the error Default location: C:\Windows\System32\SQLServerManager14.msc Its showing error like this: SQL Server Configuration Manager ————————— Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.  Solution : Open command prompt in admin mode.  Run this command " mofcomp " %programfiles(x86)%\Microsoft SQL Server\140\Shared\SqlMgmProviderXpsP2UP.mof "" Once it's get executed successfully, cmd will show success message something like this...

During development sometimes we come across the situation to get the enum specification, means list of available Name, Value or attribute description (if available).   Here are the sample methods to get these enum details: (C# code example)  /// /// Get all the values Description of this Enum /// /// enum type /// public static List GetDescrptionList () where TEnum : struct { if (!typeof(TEnum).IsEnum) { throw new ArgumentException("TEnum must be an enumerated type"); } var enumType = typeof(TEnum); var enumDescriptionList = new List (); foreach (var value in Enum.GetValues(enumType)) { var enumDescription = GetDescription((Enum)value); enumDescriptionList.Add(enumDescription); } return enumDescriptionList; } /// /// Get the description field of this Enum ...

To manage the user's roles we use Service Account on OnPrem/IaaS servers. We can use this service account on our SQL Server and accordingly manage users' permissions for the users who are part of this service account. Suppose we have an Azure SQL and we want to manage set-of-users permission shall we create each-and-every users' user profile in Azure SQL and set the permissions accordingly. Obviously, we will not .  If we've anything like Service Account (or Group) on Azure we could create the user of this GROUP on Azure SQL and set its permission/role. Here comes the Azure Active Directory Group to help us.  In case if our organization has some scheduled tasks to sync Active Directory (service account) to Azure Active Directory (AAD group), already in place, there will be some delay when we add a user to the Service account and get reflected in the AAD Group. If we have a requirement that as soon as we add a user to our group we wan...

When we try to migrate our IaaS SQL to Azure SQL along with other code changes & refactoring, one of the most prominent things that come into the picture is Authentication through a client application (web app or windows form, excel-add-ins...). With IaaS/OnPrem SQL we could use Windows Authentication to let your client application interact with the SQL server with the user's context. But to Azure SQL we can't do the Windows Authentication and also we should avoid SQL-based login (user/password).  To login to Azure SQL, it's always preferable to use Azure Active Directory-based authentication (integrated flow, MFA, or AAD based password). Now the problem comes how to achieve this with your client application.  The solution to this problem statement is, u se AAD app with delegated permission to access the users' claim [email, sid] in access token & configure it based on your client (e.g. windows form, web application) Here are ...

 Once we've created an Azure VM without an Availability set, later if we want to add it to an availability set there is no obvious option on the Azure portal. We can add an availability set only while creating the VM.  The problem comes when we try to add always-on on IaaS SQL instance one of the pre-requisite is IaaS VMs should be on an availability set. So to enable Always on existing IaaS VM either we've to re-create the VM or need to create a new one & install the required s/w from scratch.  To resolve this restriction, we can follow the below steps sequentially and achieve this.  Get the list of attached OS + Data disk of existing VM Get the Network interface details.  Remove the Azure Disk Encryption (ADE) if it's enabled.  Remove the Azure VM (delete it) Now create the new with the same name & with Availability set.  After re-creating the VM change the OS disks & Data disk...

While we're trying to connect our client application (console, windows, or web application) using Azure SQL database its preferred to use access token-based AAD authentication.  Per preferred practice to avoid NEW keyword to create objects we generally use dependency injection to create objects.  To inject the DbContext using Entity Framework Core (3.x) we have an overridden method OnConfigure() which can be used to set up the UserSQLServer() configuration to create the SQL connection. But to use UseSqlServer extension of DbContextOptionBuilder we've to create the DbConnection (or SQLConnection) after opening the SQL-connection (conn.Open()).  The problem with this approach is there is no option to dispose or close the SQL connection which we opened while creating the DbContext through DI. The below code demonstrates the general approach to achieve this. protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {...

Sometimes while trying to add/remove a disk from Azure VM through the Azure portal we are getting error like " Failed to update virtual machine disks " as below Here though we are only trying to add/remove the disk, we're not doing anything with Azure Disk Encryption (ADE), still, we're getting the error message related to ADE.  Not while adding or updating the disk, if we try to add extensions or performing any activity required to do save operation to update the VM template, it show a similar failure message which is related to ADE.  Now to resolve this if we disable the disk encryption through the Azure portal through it will disable the disk encryption and on portal, we can see there is no disk encryption and also the related Key vault field is coming up empty but still, when we try to add/remove the disk & do the save operation we are getting the similar message. This is because in the VM JSON template we can se...

On Azure, if we've windows VM and want to upgrade it to latest version e.g. Windows server 2019, there is no direct way or available template on Azure to do it smoothly. Though there is a couple of alternative to achieve this like,  Download the OS drive (c:) VHD on one of your systems, do the in-place upgrade (keeping all the files intact) and post local in-place upgrade upload the upgraded OS drive (c drive) VHD to the Azure storage account and replace the C drive of your VM.  Create the new VM all-together with the new OS version, install your required software, setup file share user permission & do all the required settings, do the thorough testing and switch the old server domain name to new one & discard the old server.  All these processes are the time-consuming activity which requires more effort from operations as well as from a testing point of view.  What if we upgrade on Azure windows VM it-self (in-place upgrade) with more confidence. Yes we can ...