Hans  Marvin

Hans Marvin

1660664665

How to Build System Databases for SQL Server on Linux & Windows

This article explores the process for rebuilding system databases – Master, MSDB, and Model for SQL Server on Linux and Windows.

Introduction

Sometimes, you cannot start a SQL Server instance because a system database (or some of them) is corrupted. In this case, it is necessary to rebuild the system databases. Rebuild operation resets the system databases and allows the instance to be online without any error. However, since you have to reset all of your data in the database, you must first ensure having system database backups.

If you rebuild your system databases without making any backup copies, you will lose your logins and jobs. And if you try to open an instance with a database after rebooting the instance, the database won’t be visible, and the SQL instance appears like a newly installed one.

The system rebuild database process is different in Windows and Linux SQL instances. Let’s explore the solution in this article.

See more at: https://www.sqlshack.com/rebuild-system-databases-for-sql-server-on-linux-and-windows/

#linux #window #sql #database #sqlserver

How to Build System Databases for SQL Server on Linux & Windows
Dang  Tu

Dang Tu

1660048020

Cách Quản Lý Nhóm Tệp Của Cơ Sở Dữ Liệu SQL Một Cách Dễ Dàng

Trong bài viết này, chúng ta sẽ tìm hiểu cách chúng ta có thể quản lý nhóm tệp của Cơ sở dữ liệu SQL trong SQL Server. Máy chủ SQL có bốn nhóm tệp.

  1. Nhóm tệp chính: Nhóm tệp chính là một nhóm tệp mặc định. Khi chúng tôi tạo cơ sở dữ liệu SQL mới, nhóm tệp chính sẽ tự động được tạo.
  2. Nhóm tệp phụ / do người dùng xác định: Nhóm tệp phụ được tạo bởi người dùng. Nhóm tệp thứ cấp được sử dụng để quản lý dữ liệu của cơ sở dữ liệu. Nếu bạn muốn giữ các bảng được truy cập nhiều của mình trong các đĩa nhanh hơn, bạn có thể tạo một nhóm tệp phụ và một bảng trong đó.
  3. Nhóm tệp được tối ưu hóa bộ nhớ: Nhóm tệp được tối ưu hóa bộ nhớ được sử dụng để lưu các bảng OLTP trong bộ nhớ và các biến bảng. Để tạo bảng được tối ưu hóa bộ nhớ trong cơ sở dữ liệu SQL, chúng ta phải tạo một nhóm tệp được tối ưu hóa bộ nhớ. Chúng ta sẽ tìm hiểu thêm về các nhóm tệp được tối ưu hóa bộ nhớ trong bài viết tiếp theo.
  4. Nhóm tệp FILESTREAM: Nhóm tệp FILESTREAM được tạo để lưu trữ dữ liệu FILESTREAM và FILETABLES. Tôi sẽ giải thích thêm về nhóm tệp FILESTRAEAM trong bài viết tiếp theo.

Nhóm tệp chứa một hoặc nhiều tệp dữ liệu. Bài viết này giải thích cách chúng tôi có thể quản lý các trường hợp sử dụng.

  1. Thêm tệp dữ liệu trong nhóm tệp
  2. Đổi tên nhóm tệp
  3. Thay đổi nhóm tệp mặc định

Để giải thích các tình huống trên, tôi đã cài đặt SQL Server 2019 trên máy tính xách tay của mình và khôi phục cơ sở dữ liệu mẫu stackoverflow2010 . Bạn có thể tải xuống cơ sở dữ liệu Stackoverflow2010 từ đây . Tôi cũng đã tạo một nhóm tệp có tên FG_POSTS trong cơ sở dữ liệu. Bạn có thể xem danh sách nhóm tệp và danh sách tệp dữ liệu được liên kết với nhóm tệp bằng cách thực hiện truy vấn sau.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Đầu ra

Xem danh sách nhóm tệp của cơ sở dữ liệu SQL

Bây giờ, chúng ta hãy hiểu tất cả các trường hợp sử dụng.

Thêm tệp dữ liệu vào một nhóm tệp

Chúng ta có thể sử dụng câu lệnh ALTER DATABASE ADD FILE TO FILEGRAOUP . Cú pháp như sau:


ALTER DATABASE <db_name> ADD FILE (NAME= <logical_file_name>, FILENAME =<file_location>, SIZE=<File_size>, FILEGROWTH= <datafile_growth>)
  To FILEGROUP <file_group_name>

Trong cú pháp,

  1. db_name: Chỉ định tên của cơ sở dữ liệu mà bạn muốn thêm tệp dữ liệu. Tên db_name phải được chỉ định sau từ khóa ALTER DATABASE.
  2. logic_file_name: Chỉ định tên logic của tệp dữ liệu thứ cấp.
  3. file_location: Chỉ định đường dẫn của tệp dữ liệu.
  4. file_size: Chỉ định kích thước ban đầu của tệp dữ liệu. Đơn vị của tham số Kích thước có thể là KB / MB / GB.
  5. datafile_growth: Chỉ định tốc độ tăng trưởng của tệp dữ liệu. Đơn vị của tham số FILEGROWTH có thể là KB / MB / GB
  6. file_group_name: Chỉ định tên của nhóm tệp mà bạn muốn thêm tệp dữ liệu.

Ví dụ: chúng tôi muốn thêm tệp dữ liệu vào nhóm tệp FG_POSTS với các tùy chọn sau

  1. tên tệp logic: fg_posts_data
  2. vị trí tệp dữ liệu: D: \ FG_Posts
  3. data_file_name: fg_posts_data.ndf
  4. Kích thước ban đầu: 10MB
  5. tăng trưởng: 5MB.

Sau đây là truy vấn để thêm tệp dữ liệu.

ALTER DATABASE [Stackoverflow2010] ADD FILE ( NAME = N'fg_posts_data', FILENAME = N'D:\FG_Posts\fg_posts_data.ndf' , SIZE = 10MB , FILEGROWTH = 5MB ) 
  TO FILEGROUP [FG_POSTS]
  GO

Sau khi lệnh được thực thi, hãy mở thành công D: \ FG_Posts để xem tệp dữ liệu.

Xem tệp dữ liệu phụ

Như bạn có thể thấy, tệp đã được tạo. Ngoài ra, bạn có thể chạy truy vấn sau để xem các nhóm tệp.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Kết quả truy vấn

Xem nhóm tệp trong Cơ sở dữ liệu SQL

Như bạn có thể thấy, tệp dữ liệu có tên fg_posts_data đã được tạo.

Bây giờ, hãy để chúng tôi hiểu cách chúng tôi có thể đổi tên nhóm tệp hiện có.

Đổi tên nhóm tệp hiện có

Bạn có thể sử dụng câu lệnh ALTER DATABASE MODIFY FILEGROUP. Sau đây là cú pháp:

ALTER DATABASE <db_name> MODIFY FILEGROUP FILEGROUP <file_group_name> NAME= <file_group_new_name>

Trong cú pháp,

  1. db_name: Chỉ định tên cơ sở dữ liệu mà nhóm tệp đã được tạo. Tên cơ sở dữ liệu phải được chỉ định sau câu lệnh ALTER DATABASE.
  2. file_group_name: Chỉ định tên nhóm tệp mà bạn muốn đổi tên.
  3. file_group_new_name: Chỉ định tên mới của nhóm tệp.

Giả sử bạn muốn đổi tên nhóm tệp có tên FG_Posts thành FG_POSTS_1 . Truy vấn đổi tên nhóm tệp lệnh như sau:

ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP FG_POSTS NAME= FG_POSTS_1

Khi lệnh được thực thi thành công, hãy chạy truy vấn sau để xác minh các thay đổi đã được áp dụng hay chưa.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Đầu ra

Nhóm tệp đã được đổi tên

Như bạn có thể thấy, nhóm tệp đã được đổi tên thành công.

Bây giờ, chúng ta hãy xem cách chúng ta có thể đánh dấu nhóm tệp phụ làm nhóm tệp mặc định.

Thay đổi nhóm tệp mặc định

Để thay đổi nhóm tệp mặc định, chúng ta có thể sử dụng câu lệnh ALTER DATABASE MODIFY FILEGROUP. Sau đây là cú pháp:

ALTER DATABASE <db_name> MODIFY FILEGROUP <file_group_name> DEFAULT

Trong cú pháp,

  1. db_name: Chỉ định tên cơ sở dữ liệu mà nhóm tệp đã được tạo. Tên cơ sở dữ liệu phải được chỉ định sau câu lệnh ALTER DATABASE.
  2. Tên_nhóm_tệp: Chỉ định tên nhóm tệp mà bạn muốn đặt làm nhóm tệp mặc định. Tên nhóm tệp phải được chỉ định sau từ khóa MODIFY FILEGROUP.
  3. DEFAULT: Từ khóa DEFAULT phải được chỉ định sau tên nhóm tệp.

Giả sử bạn muốn đặt FG_POSTS_1 làm nhóm tệp mặc định. Để làm điều đó, hãy chạy truy vấn sau.


ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP [FG_POSTS_1] DEFAULT

Khi lệnh được thực thi thành công, hãy chạy truy vấn sau để xem nhóm tệp mặc định của cơ sở dữ liệu có tên Stackoverflow2010 .

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
Case when is_default =1 then 'Default filegroup' else 'non-default filegroup' end [Is default Filegroup],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Kết quả truy vấn

Xem nhóm tệp mặc định

Như bạn có thể thấy, FG_POSTS_1 là một nhóm tệp mặc định.

Bây giờ, chúng ta hãy tạo một bảng có tên tblStackoverflow_Users. Tập lệnh để tạo bảng như sau:

 USE [Stackoverflow2010]
  GO
  CREATE TABLE [dbo].[tblStackoverflow_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL
   CONSTRAINT [PK_tblStackoverflow_Users_Id] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  ))
  GO

Bây giờ, chúng ta hãy xem bảng đã được tạo trong nhóm tệp nào. Để làm điều đó, hãy chạy truy vấn sau:

use Stackoverflow2010
  go
  SELECT OBJECT_NAME(t.object_id) AS [Table Name], d.name AS [Filegroup Name]  FROM sys.data_spaces d 
  JOIN sys.indexes i on i.data_space_id = d.data_space_id
  JOIN sys.tables t on t.object_id = i.object_id
  WHERE i.index_id<2
  AND t.type = 'U'
  AND OBJECT_NAME(t.object_id) ='tblStackoverflow_Users'

Đầu ra

Tên nhóm tệp của cơ sở dữ liệu SQL

Như bạn có thể thấy, bảng được tạo trong nhóm tệp FG_POSTS_1 .

Bản tóm tắt

Trong bài viết này, chúng ta đã tìm hiểu cách quản lý nhóm tệp trong Cơ sở dữ liệu SQL. Chúng tôi đã học được các tình huống sau:

  1. Thêm tệp dữ liệu trong nhóm tệp
  2. Đổi tên nhóm tệp
  3. Thay đổi nhóm tệp mặc định

Liên kết: https://www.sqlshack.com/managing-file-groups-of-sql-databases/

#database #sql #sqlserver

Cách Quản Lý Nhóm Tệp Của Cơ Sở Dữ Liệu SQL Một Cách Dễ Dàng
Hans  Marvin

Hans Marvin

1660040771

How to Manage File Groups of SQL Databases Easily

In this article, we are going to learn how we can manage the filegroup of SQL Database in SQL Server. The SQL Server has four filegroups.

  1. Primary Filegroup: The primary filegroup is a default filegroup. When we create a new SQL database, the Primary filegroup is automatically created.
  2. Secondary/User-defined filegroups: The secondary filegroup is created by a user. The secondary filegroup is used to manage the data of the database. If you want to keep your highly accessed tables in faster disks, you can create a secondary filegroup and a table in it.
  3. Memory-optimized filegroup: The memory-optimized filegroup is used to store the In-memory OLTP tables and table variables. To create a memory-optimized table in a SQL database, we must create a memory-optimized filegroup. We will learn more about the memory-optimized filegroups in the next article.
  4. FILESTREAM filegroup: The FILESTREAM filegroup is created to host the FILESTREAM data and FILETABLES. I will explain more about the FILESTRAEAM filegroup in the next article.

See more at: https://www.sqlshack.com/managing-file-groups-of-sql-databases/

#database #sql #sqlserver

How to Manage File Groups of SQL Databases Easily
Thierry  Perret

Thierry Perret

1660033500

Comment Gérer Les Groupes De Fichiers Des Bases De Données SQL

Dans cet article, nous allons apprendre comment gérer le groupe de fichiers de SQL Database dans SQL Server. Le serveur SQL a quatre groupes de fichiers.

  1. Groupe de fichiers principal : le groupe de fichiers principal est un groupe de fichiers par défaut. Lorsque nous créons une nouvelle base de données SQL, le groupe de fichiers principal est automatiquement créé.
  2. Groupes de fichiers secondaires/définis par l'utilisateur : le groupe de fichiers secondaire est créé par un utilisateur. Le groupe de fichiers secondaire est utilisé pour gérer les données de la base de données. Si vous souhaitez conserver vos tables à accès élevé sur des disques plus rapides, vous pouvez créer un groupe de fichiers secondaire et une table dans celui-ci.
  3. Groupe de fichiers à mémoire optimisée : le groupe de fichiers à mémoire optimisée est utilisé pour stocker les tables OLTP en mémoire et les variables de table. Pour créer une table optimisée en mémoire dans une base de données SQL, nous devons créer un groupe de fichiers optimisé en mémoire. Nous en apprendrons plus sur les groupes de fichiers à mémoire optimisée dans le prochain article.
  4. Groupe de fichiers FILESTREAM : le groupe de fichiers FILESTREAM est créé pour héberger les données FILESTREAM et FILETABLES. J'expliquerai plus sur le groupe de fichiers FILESTRAEAM dans le prochain article.

Le groupe de fichiers contient un ou plusieurs fichiers de données. Cet article explique comment nous pouvons gérer les Nous allons comprendre les cas d'utilisation suivants.

  1. Ajouter des fichiers de données dans des groupes de fichiers
  2. Renommer le groupe de fichiers
  3. Modifier le groupe de fichiers par défaut

Pour illustrer les scénarios ci-dessus, j'ai installé SQL Server 2019 sur mon ordinateur portable et restauré un exemple de base de données stackoverflow2010 . Vous pouvez télécharger la base de données Stackoverflow2010 à partir d' ici . J'ai également créé un groupe de fichiers nommé FG_POSTS dans une base de données. Vous pouvez afficher la liste des groupes de fichiers et la liste des groupes de fichiers associés aux fichiers de données en exécutant la requête suivante.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Production

Afficher la liste des groupes de fichiers de la base de données SQL

Maintenant, comprenons tous les cas d'utilisation.

Ajouter des fichiers de données dans un groupe de fichiers

Nous pouvons utiliser l'instruction ALTER DATABASE ADD FILE TO FILGRAOUP . La syntaxe est la suivante :


ALTER DATABASE <db_name> ADD FILE (NAME= <logical_file_name>, FILENAME =<file_location>, SIZE=<File_size>, FILEGROWTH= <datafile_growth>)
  To FILEGROUP <file_group_name>

Dans la syntaxe,

  1. db_name : Spécifiez le nom de la base de données dans laquelle vous souhaitez ajouter un fichier de données. Le db_name doit être spécifié après le mot-clé ALTER DATABASE.
  2. nom_fichier_logique : spécifiez le nom logique du fichier de données secondaire.
  3. emplacement_fichier : spécifiez le chemin du fichier de données.
  4. file_size : Spécifiez la taille initiale du fichier de données. L'unité du paramètre Taille peut être Ko/Mo/Go.
  5. datafile_growth : Spécifiez la croissance du fichier de données. L'unité du paramètre FILEGROWTH peut être KB/MB/GB
  6. file_group_name : indiquez le nom du groupe de fichiers dans lequel vous souhaitez ajouter un fichier de données.

Par exemple, nous voulons ajouter un fichier de données au groupe de fichiers FG_POSTS avec les options suivantes

  1. nom de fichier logique : fg_posts_data
  2. emplacement du fichier de données : D:\FG_Posts
  3. nom_fichier_données : fg_posts_data.ndf
  4. Taille initiale : 10 Mo
  5. croissance : 5 Mo.

La requête pour ajouter un fichier de données suit.

ALTER DATABASE [Stackoverflow2010] ADD FILE ( NAME = N'fg_posts_data', FILENAME = N'D:\FG_Posts\fg_posts_data.ndf' , SIZE = 10MB , FILEGROWTH = 5MB ) 
  TO FILEGROUP [FG_POSTS]
  GO

Une fois la commande exécutée, ouvrez avec succès le D:\FG_Posts pour afficher le fichier de données.

Afficher le fichier de données secondaire

Comme vous pouvez le voir, le fichier a été créé. Vous pouvez également exécuter la requête suivante pour afficher les groupes de fichiers.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Sortie de la requête

Afficher le groupe de fichiers dans la base de données SQL

Comme vous pouvez le voir, le fichier de données nommé fg_posts_data a été créé.

Maintenant, comprenons comment nous pouvons renommer le groupe de fichiers existant.

Renommer le groupe de fichiers existant

Vous pouvez utiliser l'instruction ALTER DATABASE MODIFY FILEGROUP. Voici la syntaxe :

ALTER DATABASE <db_name> MODIFY FILEGROUP FILEGROUP <file_group_name> NAME= <file_group_new_name>

Dans la syntaxe,

  1. db_name : spécifiez le nom de la base de données dans laquelle le groupe de fichiers a été créé. Le nom de la base de données doit être spécifié après l'instruction ALTER DATABASE.
  2. nom_groupe_fichiers : spécifiez le nom du groupe de fichiers que vous souhaitez renommer.
  3. file_group_new_name : spécifiez le nouveau nom du groupe de fichiers.

Supposons que vous souhaitiez renommer le groupe de fichiers nommé FG_Posts en FG_POSTS_1 . La requête pour renommer le groupe de fichiers est la suivante :

ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP FG_POSTS NAME= FG_POSTS_1

Une fois la commande exécutée avec succès, exécutez la requête suivante pour vérifier que les modifications ont été appliquées ou non.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Production

Le groupe de fichiers a été renommé

Comme vous pouvez le voir, le groupe de fichiers a été renommé avec succès.

Voyons maintenant comment marquer un groupe de fichiers secondaire comme groupe de fichiers par défaut.

Modifier le groupe de fichiers par défaut

Pour changer le groupe de fichiers par défaut, nous pouvons utiliser l'instruction ALTER DATABASE MODIFY FILEGROUP. Voici la syntaxe :

ALTER DATABASE <db_name> MODIFY FILEGROUP <file_group_name> DEFAULT

Dans la syntaxe,

  1. db_name : spécifiez le nom de la base de données dans laquelle le groupe de fichiers a été créé. Le nom de la base de données doit être spécifié après l'instruction ALTER DATABASE.
  2. File_group_name : spécifiez le nom du groupe de fichiers que vous souhaitez définir comme groupe de fichiers par défaut. Le nom du groupe de fichiers doit être spécifié après le mot-clé MODIFY FILEGROUP.
  3. DEFAULT : le mot clé DEFAULT doit être spécifié après le nom du groupe de fichiers.

Supposons que vous souhaitiez définir FG_POSTS_1 comme groupe de fichiers par défaut. Pour ce faire, exécutez la requête suivante.


ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP [FG_POSTS_1] DEFAULT

Une fois la commande exécutée avec succès, exécutez la requête suivante pour afficher le groupe de fichiers par défaut d'une base de données nommée Stackoverflow2010 .

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
Case when is_default =1 then 'Default filegroup' else 'non-default filegroup' end [Is default Filegroup],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Sortie de la requête

Afficher le groupe de fichiers par défaut

Comme vous pouvez le voir, le FG_POSTS_1 est un groupe de fichiers par défaut.

Maintenant, créons une table nommée tblStackoverflow_Users. Le script pour créer la table est le suivant :

 USE [Stackoverflow2010]
  GO
  CREATE TABLE [dbo].[tblStackoverflow_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL
   CONSTRAINT [PK_tblStackoverflow_Users_Id] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  ))
  GO

Voyons maintenant dans quel groupe de fichiers la table a été créée. Pour ce faire, exécutez la requête suivante :

use Stackoverflow2010
  go
  SELECT OBJECT_NAME(t.object_id) AS [Table Name], d.name AS [Filegroup Name]  FROM sys.data_spaces d 
  JOIN sys.indexes i on i.data_space_id = d.data_space_id
  JOIN sys.tables t on t.object_id = i.object_id
  WHERE i.index_id<2
  AND t.type = 'U'
  AND OBJECT_NAME(t.object_id) ='tblStackoverflow_Users'

Production

Nom du groupe de fichiers de la base de données SQL

Comme vous pouvez le voir, la table est créée dans le groupe de fichiers FG_POSTS_1 .

Sommaire

Dans cet article, nous avons appris comment gérer les groupes de fichiers dans SQL Database. Nous avons appris les scénarios suivants :

  1. Ajouter des fichiers de données dans des groupes de fichiers
  2. Renommer le groupe de fichiers
  3. Modifier le groupe de fichiers par défaut

Lien : https://www.sqlshack.com/managing-file-groups-of-sql-databases/

#database #sql #sqlserver

Comment Gérer Les Groupes De Fichiers Des Bases De Données SQL

Как легко управлять файловыми группами баз данных SQL

В этой статье мы узнаем, как управлять файловой группой базы данных SQL в SQL Server. SQL Server имеет четыре файловые группы.

  1. Первичная файловая группа: первичная файловая группа является файловой группой по умолчанию. Когда мы создаем новую базу данных SQL, основная файловая группа создается автоматически.
  2. Вторичные/определяемые пользователем файловые группы: Вторичная файловая группа создается пользователем. Вторичная файловая группа используется для управления данными базы данных. Если вы хотите хранить часто используемые таблицы на более быстрых дисках, вы можете создать дополнительную файловую группу и таблицу в ней.
  3. Файловая группа , оптимизированная для памяти. Файловая группа, оптимизированная для памяти, используется для хранения таблиц OLTP в памяти и табличных переменных. Чтобы создать таблицу, оптимизированную для памяти, в базе данных SQL, мы должны создать файловую группу, оптимизированную для памяти. Мы узнаем больше о файловых группах, оптимизированных для памяти, в следующей статье.
  4. Файловая группа FILESTREAM. Файловая группа FILESTREAM создается для размещения данных FILESTREAM и FILETABLES . Подробнее о файловой группе FILESTREAM я расскажу в следующей статье.

Файловая группа содержит один или несколько файлов данных. В этой статье объясняется, как мы можем управлять. Мы собираемся понять следующие варианты использования.

  1. Добавить файлы данных в файловые группы
  2. Переименуйте файловую группу
  3. Изменить файловую группу по умолчанию

Чтобы продемонстрировать описанные выше сценарии, я установил SQL Server 2019 на свой ноутбук и восстановил образец базы данных stackoverflow2010 . Скачать базу данных Stackoverflow2010 можно отсюда . Я также создал файловую группу с именем FG_POSTS в базе данных. Вы можете просмотреть список файловых групп и список файлов данных, связанных с файловой группой, выполнив следующий запрос.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Выход

Просмотр списка файловых групп базы данных SQL

Теперь давайте разберемся со всеми вариантами использования.

Добавить файлы данных в файловую группу

Мы можем использовать оператор ALTER DATABASE ADD FILE TO FILEGRAOUP . Синтаксис следующий:


ALTER DATABASE <db_name> ADD FILE (NAME= <logical_file_name>, FILENAME =<file_location>, SIZE=<File_size>, FILEGROWTH= <datafile_growth>)
  To FILEGROUP <file_group_name>

В синтаксисе

  1. db_name: Укажите имя базы данных, в которую вы хотите добавить файл данных. Имя_базы_данных должно быть указано после ключевого слова ALTER DATABASE.
  2. logical_file_name: укажите логическое имя вторичного файла данных.
  3. file_location: укажите путь к файлу данных.
  4. file_size: укажите начальный размер файла данных. Единицей измерения параметра Размер может быть КБ/МБ/ГБ.
  5. datafile_growth: укажите рост файла данных. Единицей измерения параметра FILEGROWTH может быть КБ/МБ/ГБ.
  6. file_group_name: Укажите имя файловой группы, в которую вы хотите добавить файл данных.

Например, мы хотим добавить файл данных в файловую группу FG_POSTS со следующими параметрами

  1. логическое имя файла: fg_posts_data
  2. расположение файла данных: D:\FG_Posts
  3. data_file_name: fg_posts_data.ndf
  4. начальный размер: 10 МБ
  5. рост: 5MB.

Запрос на добавление файла данных следующий.

ALTER DATABASE [Stackoverflow2010] ADD FILE ( NAME = N'fg_posts_data', FILENAME = N'D:\FG_Posts\fg_posts_data.ndf' , SIZE = 10MB , FILEGROWTH = 5MB ) 
  TO FILEGROUP [FG_POSTS]
  GO

После выполнения команды успешно откройте D:\FG_Posts для просмотра файла данных.

Просмотр вторичного файла данных

Как видите, файл создан. Кроме того, вы можете запустить следующий запрос для просмотра файловых групп.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Вывод запроса

Просмотр файловой группы в базе данных SQL

Как видите, файл данных с именем fg_posts_data создан.

Теперь давайте разберемся, как мы можем переименовать существующую файловую группу.

Переименовать существующую файловую группу

Вы можете использовать инструкцию ALTER DATABASE MODIFY FILEGROUP. Ниже приведен синтаксис:

ALTER DATABASE <db_name> MODIFY FILEGROUP FILEGROUP <file_group_name> NAME= <file_group_new_name>

В синтаксисе

  1. db_name: укажите имя базы данных, в которой была создана файловая группа. Имя базы данных должно быть указано после инструкции ALTER DATABASE.
  2. file_group_name: Укажите имя файловой группы, которую вы хотите переименовать.
  3. file_group_new_name: укажите новое имя файловой группы.

Предположим, вы хотите переименовать файловую группу с именем FG_Posts в FG_POSTS_1 . Запрос на переименование файловой группы командой следующий:

ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP FG_POSTS NAME= FG_POSTS_1

После успешного выполнения команды выполните следующий запрос, чтобы проверить, были ли применены изменения.

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Выход

Файловая группа была переименована

Как видите, файловая группа успешно переименована.

Теперь давайте посмотрим, как мы можем пометить вторичную файловую группу как файловую группу по умолчанию.

Изменить файловую группу по умолчанию

Чтобы изменить файловую группу по умолчанию, мы можем использовать оператор ALTER DATABASE MODIFY FILEGROUP. Ниже приведен синтаксис:

ALTER DATABASE <db_name> MODIFY FILEGROUP <file_group_name> DEFAULT

В синтаксисе

  1. db_name: укажите имя базы данных, в которой была создана файловая группа. Имя базы данных должно быть указано после инструкции ALTER DATABASE.
  2. File_group_name: Укажите имя файловой группы, которую вы хотите сделать файловой группой по умолчанию. Имя файловой группы должно быть указано после ключевого слова MODIFY FILEGROUP.
  3. DEFAULT: Ключевое слово DEFAULT должно быть указано после имени файловой группы.

Предположим, вы хотите установить FG_POSTS_1 в качестве файловой группы по умолчанию. Для этого выполните следующий запрос.


ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP [FG_POSTS_1] DEFAULT

После успешного выполнения команды выполните следующий запрос, чтобы просмотреть файловую группу по умолчанию для базы данных с именем Stackoverflow2010 .

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
Case when is_default =1 then 'Default filegroup' else 'non-default filegroup' end [Is default Filegroup],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

Вывод запроса

Просмотр файловой группы по умолчанию

Как видите, FG_POSTS_1 — это файловая группа по умолчанию.

Теперь давайте создадим таблицу с именем tblStackoverflow_Users. Скрипт для создания таблицы следующий:

 USE [Stackoverflow2010]
  GO
  CREATE TABLE [dbo].[tblStackoverflow_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL
   CONSTRAINT [PK_tblStackoverflow_Users_Id] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  ))
  GO

Теперь давайте посмотрим, в какой файловой группе была создана таблица. Для этого выполните следующий запрос:

use Stackoverflow2010
  go
  SELECT OBJECT_NAME(t.object_id) AS [Table Name], d.name AS [Filegroup Name]  FROM sys.data_spaces d 
  JOIN sys.indexes i on i.data_space_id = d.data_space_id
  JOIN sys.tables t on t.object_id = i.object_id
  WHERE i.index_id<2
  AND t.type = 'U'
  AND OBJECT_NAME(t.object_id) ='tblStackoverflow_Users'

Выход

Имя файловой группы базы данных SQL

Как видите, таблица создана в файловой группе FG_POSTS_1 .

Резюме

В этой статье мы узнали, как управлять файловыми группами в базе данных SQL. Мы изучили следующие сценарии:

  1. Добавить файлы данных в файловые группы
  2. Переименуйте файловую группу
  3. Изменить файловую группу по умолчанию

Ссылка: https://www.sqlshack.com/managing-file-groups-of-sql-databases/

#database #sql #sqlserver

Как легко управлять файловыми группами баз данных SQL
田辺  亮介

田辺 亮介

1660018964

如何輕鬆管理 SQL 數據庫的文件組

在本文中,我們將學習如何在 SQL Server 中管理 SQL 數據庫的文件組。SQL Server 有四個文件組。

  1. 主文件組:主文件組是默認文件組。當我們創建一個新的 SQL 數據庫時,會自動創建主文件組。
  2. 輔助/用戶定義的文件組:輔助文件組由用戶創建。輔助文件組用於管理數據庫的數據。如果您想將高度訪問的表保存在更快的磁盤中,您可以在其中創建一個輔助文件組和一個表。
  3. 內存優化文件組:內存優化文件組用於存儲 In-memory OLTP 表和表變量。要在 SQL 數據庫中創建內存優化表,我們必須創建內存優化文件組。我們將在下一篇文章中了解有關內存優化文件組的更多信息。
  4. FILESTREAM 文件組:創建 FILESTREAM 文件組是為了託管 FILESTREAM 數據和 FILETABLES。我將在下一篇文章中詳細解釋 FILESTRAEAM 文件組。

文件組包含一個或多個數據文件。本文解釋了我們如何管理我們將了解以下用例。

  1. 在文件組中添加數據文件
  2. 重命名文件組
  3. 更改默認文件組

為了演示上述場景,我在筆記本電腦上安裝了 SQL Server 2019 並恢復了stackoverflow2010示例數據庫。您可以從這裡下載 Stackoverflow2010 數據庫。我還在數據庫中創建了一個名為FG_POSTS的文件組。您可以通過執行以下查詢來查看文件組列表和與文件組關聯的數據文件列表。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

輸出

查看 SQL 數據庫的文件組列表

現在,讓我們了解所有用例。

在文件組中添加數據文件

我們可以使用ALTER DATABASE ADD FILE TO FILEGRAOUP語句。語法如下:


ALTER DATABASE <db_name> ADD FILE (NAME= <logical_file_name>, FILENAME =<file_location>, SIZE=<File_size>, FILEGROWTH= <datafile_growth>)
  To FILEGROUP <file_group_name>

在語法中,

  1. db_name:指定要在其中添加數據文件的數據庫的名稱。db_name 必須在 ALTER DATABASE 關鍵字之後指定。
  2. logical_file_name:指定輔助數據文件的邏輯名稱。
  3. file_location:指定數據文件的路徑。
  4. file_size:指定數據文件的初始大小。Size參數的單位可以是KB/MB/GB。
  5. datafile_growth:指定數據文件的增長。FILEGROWTH 參數的單位可以是 KB/MB/GB
  6. file_group_name:指定要在其中添加數據文件的文件組的名稱。

例如,我們想使用以下選項將數據文件添加到 FG_POSTS 文件組

  1. 邏輯文件名: fg_posts_data
  2. 數據文件位置: D:\FG_Posts
  3. 數據文件名: fg_posts_data.ndf
  4. 初始大小: 10MB
  5. 增長: 5MB。

添加數據文件的查詢如下。

ALTER DATABASE [Stackoverflow2010] ADD FILE ( NAME = N'fg_posts_data', FILENAME = N'D:\FG_Posts\fg_posts_data.ndf' , SIZE = 10MB , FILEGROWTH = 5MB ) 
  TO FILEGROUP [FG_POSTS]
  GO

命令執行後,成功打開D:\FG_Posts即可查看數據文件。

查看輔助數據文件

如您所見,文件已創建。或者,您可以運行以下查詢來查看文件組。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

查詢輸出

查看 SQL 數據庫中的文件組

如您所見,名為fg_posts_data的數據文件已創建。

現在,讓我們了解如何重命名現有文件組。

重命名現有文件組

您可以使用 ALTER DATABASE MODIFY FILEGROUP 語句。以下是語法:

ALTER DATABASE <db_name> MODIFY FILEGROUP FILEGROUP <file_group_name> NAME= <file_group_new_name>

在語法中,

  1. db_name:指定創建文件組的數據庫名稱。必須在 ALTER DATABASE 語句之後指定數據庫名稱。
  2. file_group_name:指定要重命名的文件組名稱。
  3. file_group_new_name:指定文件組的新名稱。

假設您要將名為 FG_Posts 的文件組重命名FG_POSTS_1。重命名文件組的查詢命令如下:

ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP FG_POSTS NAME= FG_POSTS_1

成功執行命令後,運行以下查詢以驗證更改是否已應用。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

輸出

文件組已重命名

如您所見,文件組已成功重命名。

現在,讓我們看看如何將輔助文件組標記為默認文件組。

更改默認文件組

要更改默認文件組,我們可以使用 ALTER DATABASE MODIFY FILEGROUP 語句。以下是語法:

ALTER DATABASE <db_name> MODIFY FILEGROUP <file_group_name> DEFAULT

在語法中,

  1. db_name:指定創建文件組的數據庫名稱。必須在 ALTER DATABASE 語句之後指定數據庫名稱。
  2. File_group_name:指定要作為默認文件組的文件組名稱。必須在 MODIFY FILEGROUP 關鍵字之後指定文件組名稱。
  3. DEFAULT:必須在文件組名稱之後指定 DEFAULT 關鍵字。

假設您要將FG_POSTS_1設置為默認文件組。為此,請運行以下查詢。


ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP [FG_POSTS_1] DEFAULT

成功執行命令後,運行以下查詢以查看名為Stackoverflow2010的數據庫的默認文件組。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
Case when is_default =1 then 'Default filegroup' else 'non-default filegroup' end [Is default Filegroup],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id  

查詢輸出

查看默認文件組

如您所見,FG_POSTS_1是默認文件組。

現在,讓我們創建一個名為 tblStackoverflow_Users 的表。創建表的腳本如下:

 USE [Stackoverflow2010]
  GO
  CREATE TABLE [dbo].[tblStackoverflow_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL
   CONSTRAINT [PK_tblStackoverflow_Users_Id] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  ))
  GO

現在,讓我們看看表是在哪個文件組中創建的。為此,請運行以下查詢:

use Stackoverflow2010
  go
  SELECT OBJECT_NAME(t.object_id) AS [Table Name], d.name AS [Filegroup Name]  FROM sys.data_spaces d 
  JOIN sys.indexes i on i.data_space_id = d.data_space_id
  JOIN sys.tables t on t.object_id = i.object_id
  WHERE i.index_id<2
  AND t.type = 'U'
  AND OBJECT_NAME(t.object_id) ='tblStackoverflow_Users'

輸出

SQL 數據庫的文件組名稱

如您所見,該表是在FG_POSTS_1文件組中創建的。

概括

在本文中,我們了解瞭如何管理 SQL 數據庫中的文件組。我們學習了以下場景:

  1. 在文件組中添加數據文件
  2. 重命名文件組
  3. 更改默認文件組

鏈接:https ://www.sqlshack.com/managing-file-groups-of-sql-databases/

#database #sql #sqlserver

如何輕鬆管理 SQL 數據庫的文件組
Dylan  Iqbal

Dylan Iqbal

1656056975

SQL NULL values | How to handle NULL in SQL

How to handle NULL in SQL

The NULL value represents missing or not applicable information. But using it introduces three-valued logic. This can lead to surprising outcomes when handle...


Working with SQL NULL values

This article will explain what problems can occur when working with SQL NULL values ​​and it also gives some solution recommendations to overcome these issues.

What is a SQL NULL value?

In terms of the relational database model, a NULL value indicates an unknown value. If we widen this theoretical explanation, the NULL value points to an unknown value but this unknown value does not equivalent to a zero value or a field that contains spaces. Due to this structure of the NULL values, it is not possible to use traditional comparison (=, <, > and <>) operators in the queries. As a matter of fact, in the SQL Standards using the WHERE clause as the below will lead to return empty result sets.

SELECT column_name1, column_name2, column_name3, ... , column_nameN
FROM table_name
WHERE column_nameN = NULL

For this reason, working with the NULL values might be a bit complicated and are required to use certain built-in functions which are customized for handling NULL values.

IS NULL Condition

The IS NULL condition is used to return rows that contain the NULL values in a column and its syntax is like below:

SELECT column_name1, column_name2, column_name3, ... , column_nameN
FROM table_name
WHERE column_nameN IS NULL

The following query will retrieve the rows from the Person table which are MiddleName column values are equal to NULL.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName IS NULL
How to retrieve SQL NULL values to use IS NULL expression

The IS NOT NULL condition is used to return the rows that contain non-NULL values in a column. The following query will retrieve the rows from the Person table which are MiddleName column value is not equal to NULL values.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName IS NOT NULL
How to use IS NOT NULL expression

Handling SQL NULL values with Functions

As we stated earlier, SQL Server offers some functions that help to handle NULL values.

ISNULL(): The ISNULL() function takes two parameters and it enables us to replace NULL values with a specified value.

ISNULL (expression, replacement)

The expression parameter indicates the expression which we want to check NULL values.

The replacement parameter indicates the value which we want to replace the NULL values.

For example, in the following query, the ISNULL() function replaces the NULL values in the row with the specified value.

SELECT Title,ISNULL(Title,'NewTitle') AS NewTitle , FirstName,LastName
FROM Person.Person WHERE BusinessEntityID = 74
Handling SQL NULL values with ISNULL function

COALESCE(): The COALESCE() function takes unlimited parameters and returns the first non-null expression in a list.

COALESCE(val1, val2, ...., val_n)

In the following query, the COALESCE() function returns the SQLShack.com because it is the first non-null value in the list.

SELECT COALESCE(NULL, NULL, 'SQLShack.com', NULL, 'Best Articles')
Handling SQL NULL values with COALESCE function

In this second example, the COALESCE() function returns the first non-null expression in the list of the columns.

SELECT BusinessEntityID,FirstName,LastName,Suffix,Title,
COALESCE(Title,Suffix,FirstName) AS NewValue
FROM Person.Person WHERE BusinessEntityID IN (5, 74)
COALESCE function usage

How to Count SQL NULL values in a column?

The COUNT() function is used to obtain the total number of the rows in the result set. When we use this function with the star sign it count all rows from the table regardless of NULL values. Such as, when we count the Person table through the following query, it will return 19972.

SELECT COUNT(*) AS [Total Number of Rows] FROM Person.Person
How to count SQL NULL values in a table

On the other hand, when we use the COUNT() function with a column name it counts only the non-NULL values in that column.

SELECT COUNT(Title) AS [Total Number of Title] FROM Person.Person
COUNT() function and SQL NULL values interaction

In order to count NULL values of a column, we can use the following query.

SELECT SUM(CASE WHEN Title is null THEN 1 ELSE 0 END) 
AS [Number Of Null Values] 
    , COUNT(Title) AS [Number Of Non-Null Values] 
    FROM Person.Person
How to count null values in a column

AVG() function and SQL NULL values

The AVG () is used to calculate the average value of a result set, that is, it sums all the values ​​in that result set and divides that sum by the number of rows. One point to note about the AVG() function calculation is that NULL values will not be included in the average calculation.

CREATE TABLE DailyFee
(WeekDayName VARCHAR(50), Fee MONEY DEFAULT NULL)
 
INSERT INTO DailyFee
VALUES
('Monday', 20 ), 
( 'Tuesday', 60 ),
( 'Wednesday', NULL ), 
(  'Thurusday', 40 ),
(  'Friday', 80 )

When we calculate the weekly average fee, the Wednesday fee is not included in the calculation.

SELECT * FROM DailyFee
 
SELECT AVG(Fee) AS AverageFee FROM  DailyFee
NULL values affect AVG function  results

If we want to include the NULL values in the calculation, we can use the COALESCE function.

SELECT * FROM DailyFee
 
SELECT AVG(COALESCE(Fee,0)) AS AverageFee FROM  DailyFee
NULL values and AVG function  interaction

What is the difference between ‘IS NULL’ and ‘= NULL’?

The equal (=) operator is used to compare two expressions values and if the compared values are equal the comparison result will be true. On the other hand, when we try to use an equal operator to find the rows that contain the null values, the result set will return nothing. For example, the following query will not return any rows.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName = NULL
SET ANSI NULL option and NULL function

The reason for this issue is related to the structure of the SQL NULL because it does not specify an explicit value thus comparing an unknown value with an exact value result will be false.

According to ANSI (American National Standard Institute) standards, any comparison or calculation performed with NULL values result must be NULL as we have just stated above. However, we can disable this behavior pattern by using the SET ANSI_NULLS command. By default, this option status is ON but we can disable this option during the session-level so the equals (=) and not equal (<>) comparison operators do not apply to the ANSI-SQL standard.

SET ANSI_NULLS OFF
 
SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName = NULL

As seen above, the select statement returns the rows that have null values in the MiddleName column when we disabled the ANSI_NULLS option

Non-Clustered Indexes and SQL NULL values

The indexes can help to improve the performance of the queries but at this point, one question may appear in our minds.

“Can we use non-clustered indexes for the nullable columns in SQL Server and these indexes can improve performance of our queries?”

Let’s try to find out an answer to this with an example.

When we execute the following query, the query optimizer suggests a non-clustered index and it claims that this index dramatically improves the performance of the query.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName IS NULL
NULL values  query plan

As seen in the execution plan, the optimizer has decided to use a non-clustered index scan operator, and it reads 107 data pages from the data cache. Now, we will apply the missing index suggestion.

USE [AdventureWorks2017]
GO
CREATE NONCLUSTERED INDEX IX_001_MiddleName
ON [Person].[Person] ([MiddleName])
INCLUDE ([FirstName],[LastName])
GO

In this step, we will re-execute the same query and re-analyze the execution plan and I/O statistics.

SET STATISTICS IO ON
SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
MiddleName IS NULL
Index seek and query plan
I/O statistics of an optimized query

Without a doubt, the created index is used by the database engine and it boosts the performance of the query because the logical reads number is lower than the previous one.

The index seek operator details show us there is not any difference between the Actual Number of Rows and the Estimated Number of Rows.

Index Seek operator properties

This situation shows that the query optimizer finds accurate information about how many NULL rows are stored in the MiddleName column. Most likely, the following question will pop up in your mind.

“How does the SQL Server query optimizer know how many NULL rows are stored for a column in a table?”

Basically, the statistics store the data distribution about the tables, and the query optimizer makes use of this information during the creation of a query plan. When we create an index this data distribution information is also stored for the NULL valued columns. So that, optimizer estimates how much memory and other resources are required for a query

The DBCC SHOW_STATISTICS command gives us detailed information about the statistics and data distributions. Through the following query, we will return all details about the used index statistics for the IX_001_MiddleName index.

DBCC SHOW_STATISTICS ('Person.Person','IX_001_MiddleName')
Statistics details of a index

As we can see there is an 8499 number of NULL values are stored in the MiddleName column and this information is used by the query optimizer.

In some cases, the ISNULL function is used with the where condition but this usage method may lead to use indexes inefficiently. The purpose of the following query is to fetch rows of the MiddleName column whose values are equal to A or NULL. However, this query cannot use the created non-clustered index so it will read all index pages and then return the appropriate rows.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE ISNULL(MiddleName,'A')='A'
How to improve performance of the ISNULL function

To eliminate this situation, we can make a little code modification in the query so that the query optimizer can use the indexes more efficiently. The following query returns some rows as like the previous one but it uses the indexes more efficiently.

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE 
(MiddleName IS NULL OR MiddleName ='A')
How to improve performance of the SQL NULL values

Conclusion

In this article, we have learned how to work with SQL NULL values and have learned its performance impact on the queries.


Different Ways To Handle NULL In SQL Server

In this tutorial, I am going to explain various methods to handle NULLs in SQL Server. This detailed article will cover the following topics as follows:

  1. Introduction
  2. What exactly is NULL?
  3. Different Ways to handle NULLs
  4. Difference between ISNULL and COALESCE Function
  5. Conclusion

What Exactly Is NULL?

A NULL is a term with a value other than 0 or an empty string, i.e. an unknown value that data is missing.

As per Wikipedia, "Null or NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. A null should not be confused with a value of 0. A null value indicates a lack of a value, which is not the same thing as a value of zero. SQL null is a state, not a value."

First, let's create a database with a table containing some dummy data. Here, I am providing you with the database along with the table containing the records, on which I am showing you the various examples. Let's see.

CREATE TABLE HandleISNULL(
	EmployeeID INT IDENTITY (31100,11),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	EmployeeFullName VARCHAR (100) NOT NULL,
	DefaultTask VARCHAR(100),
	Salary BIGINT,
	PrimaryContactNo BIGINT,
	SecondaryContactNo BIGINT,
	Telephone INT
	PRIMARY KEY (EmployeeID)
)

Now, insert some dummy data in it.

INSERT INTO HandleISNULL VALUES
(DEFAULT, 'Prabhas', 'Development', 2100000, 789654123, 741258963, NULL),
(DEFAULT, 'John Sinha', NULL, NULL, 123654789, NULL, NULL),
(DEFAULT, 'Meenu Tiwari', 'HR', 1800000, 456987132, 963258741, NULL),
(DEFAULT, 'Neeraj__Chopra', NULL, NULL, 951478632, NULL, NULL),
(DEFAULT, 'Neeraj Chopra', 'Marketing', 900000, 485936217, NULL, NULL),
(DEFAULT, 'Onkar Sharma', 'Admin', 5200000,369147852, 326159874, NULL),
(DEFAULT, 'Bajrang Punia', 'HR', 1700000, 98653247, 784512369, NULL),
(DEFAULT, 'Satish', 'Development', 2300000, NULL, NULL, NULL),
(DEFAULT, 'Parneeti Dhoppra', 'Support', 700000, 123654789, 48963157, NULL),
(DEFAULT, 'Ananya Pandey', NULL, 300000, 547896321, NULL,NULL),
(DEFAULT, 'Shraddha Kapoor', 'Account', 1500000, NULL, NULL, 265819),
(DEFAULT, 'Akshay Sharma', NULL, NULL, 648521789, NULL,NULL)

Let's check our following table by using the following query.

SELECT * FROM OnkarSharma..HandleISNULL
different-ways-to-handle-null-in-sql-server

Note
Here, we can see that symbols “=” and “!=” do not work with “NULL” values as follows.

-- Wrong Query
SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask = NULL

SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask != NULL
different-ways-to-handle-null-in-sql-server
-- Right Query
SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask IS NULL

SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask IS NOT NULL
different-ways-to-handle-null-in-sql-server

ISNULL() Function

The ISNULL() returns a specified replacement value if the expression is NULL. It replaces NULL with the specified replacement value if the expression is NULL.

Key Points

  • ISNULL() takes only two parameters.
  • ISNULL() is Microsoft SQL Server-specific.
  • The ISNULL() return value is always considered NON-NULLable.
  • The first parameter's data type determines the data type of the value returned by ISNULL().

Syntax

ISNULL(Expression, ReplacementValue);

Examples

The examples in this section demonstrate the functionality of the ISNULL() function. Let's see.

Simple Example

SELECT ISNULL (NULL, 108) NULL_REPLACEMENT;
SELECT ISNULL (NULL,'SQLServer') NULL_REPLACEMENT;
SELECT ISNULL ('OnkarSharma','SQLServer') NULL_REPLACEMENT;
SELECT ISNULL (NULL,GETDATE()) NULL_REPLACEMENT;
different-ways-to-handle-null-in-sql-server

Complex Example

Replace the NULL with custom values/messages.

The following example returns the list of all employees who did not provide an alternate phone/contact number.

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
ISNULL(DefaultTask, 'Trainee') AS DefaultTask, PrimaryContactNo,
ISNULL(CAST(SecondaryContactNo AS VARCHAR), 'NOT Available') AS 'Secondary Contact No'
FROM OnkarSharma..HandleISNULL
WHERE SecondaryContactNo IS NULL
different-ways-to-handle-null-in-sql-server

COALESCE() Function

The COALESCE() function returns the first NON-NULL value from the list provided. As per Wikipedia, "The COALESCE function accepts a list of parameters, returning the first non-null value from the list".

Key Points

  • COALESCE is an ANSI standard function.
  • COALESCE() function can contain multiple expressions.
  • Expressions in COALESCE() must be of the same data type.
  • The Coalesce function is a syntactic shorthand for the SQL Case expression.
  • It always evaluates for an integer first, an integer followed by character expression yields an integer as an output.
  • Returns the data type of expression/value with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.

Syntax

COALESCE (expression1, expression2,... expressionN);

Examples

The examples in this section demonstrate the functionality of the COALESCE() Function. Let's see.

Simple Example

SELECT COALESCE (NULL,'A','B') NULL_REPLACEMENT
SELECT COALESCE (NULL,100,120,130,140) NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,521,NULL,NULL) NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,'','C-sharpcorner') NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,NULL,'Onkar Sharma') NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,NULL,1,'Onkar Sharma MVP') NULL_REPLACEMENT
different-ways-to-handle-null-in-sql-server

Note
Note that, NULL and blank space is not the same. In the above example, COALESCE returns a blank space showing us that a blank space (empty string) is the first non-NULL value in the list.

Complex Example

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
COALESCE(CAST(PrimaryContactNo AS VARCHAR), CAST(SecondaryContactNo AS VARCHAR), CAST(Telephone AS VARCHAR), 'NOT Available') AS 'Available Contact Number'
FROM OnkarSharma..HandleISNULL
different-ways-to-handle-null-in-sql-server

CASE Statement

The Case Statement in SQL Server is used to evaluate a list of conditions and return one of the possible result expressions. The CASE Statements has two formats, viz, Simple CASE expression and Searched CASE expression.

Key Points

  • CASE can be used in any statement or clause that allows a valid expression.
  • SQL Server allows only 10 levels of nesting in CASE expressions.

Syntax

Simple CASE expression

CASE [input_expression]
WHEN [expression] THEN [result_expression] ELSE [else_expression]
END

Searched CASE expression

CASE
WHEN [expression] THEN [result_expression]
ELSE [else_expression]
END

Examples

The examples in this section demonstrate the functionality of the CASE statement. Let's see.

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
CASE WHEN DefaultTask IS NULL THEN '*Trainee' ELSE DefaultTask END AS DefaultTask,
CASE WHEN Salary IS NULL THEN 360000 ELSE Salary END AS Salary
FROM OnkarSharma..HandleISNULL
different-ways-to-handle-null-in-sql-server

Difference Between ISNULL and COALESCE Functions

Now, let's see the quick difference between ISNULL() and COALESCE() Functions

S.No.ISNULL()COALESCE()
1ISNULL() is Microsoft SQL Server-specific.COALESCE is an ANSI standard function.
2ISNULL() takes only two parameters.COALESCE() Function can contain multiple argunments/expressions.
3The COALESCE expression is a syntactic shortcut for the CASE expression.Expressions in COALESCE() must be of the same data type.
4The first argument's data type determines the data type of the value returned by ISNULL.Returns the data type of expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.
5The ISNULL return value is always considered NON-NULLable.COALESCE with non-null parameters is considered to be NULL.

Conclusion

In this article, we have discussed various methods to handle NULLs in SQL Server.

Thanks for reading.

#sql #sqlserver #database 

SQL NULL values | How to handle NULL in SQL
Coding  Fan

Coding Fan

1655699024

How to Create ASP.NET Web API Application Which Deals with a SQL Server Database

In this video we will create ASP.NET Web API application which deals with a SQL Server database. We will be using this application just to understand the ASP.NET Web API concepts and its most commonly used methods.
This tutorial will help you to understand the Web API Insert Update Delete methods in Asp.net WEB API with Sql Server.

Introduction To ASP .NET Web API :
The term API stands for Application Programming Interface. ASP.NET Web API is a framework for building Web API’s, i.e. HTTP based services on top of the .NET Framework. The most common use case for using Web API is for building RESTful services.

What are RESTful services :
REST stands for Representational State Transfer. REST was first introduced in the year 2000 by Roy Fielding as part of his doctoral dissertation. 
REST is an architectural pattern for creating an API that uses HTTP as its underlying communication method. The REST architectural pattern specifies a set of constraints that a system should adhere to.

Subscribe: https://www.youtube.com/c/ManojDeshwal/featured 

#aspdotnet #sqlserver   

How to Create ASP.NET Web API Application Which Deals with a SQL Server Database
Coding  Fan

Coding Fan

1655698517

Web API Tutorial | Web API Crud Operations with Entity Framework & SQL Server

In this tutorial I will explain about web api from scratch. What is web api and how to create web api project and how to create 
methods in web api project and how to perform crud operations in web api. So I am gonna cover all this in this tutorial. This tutorial will help you to understand what is web api and also help you how to create a project in web api and how to create a get method on web api and how to create a post method in web api and how to create put method in web api and how to create a delete method in web api. So this tutorial is going to be very helpful to you. After this tutorial you will have the complete understanding about the basics of web api topic and you would be able to create your own apis and further you can consume the apis as per your requirements. I have also used entity framework in this tutorial so you will also have the idea about the Entity Framework in aps.net web api which will be very useful to you and to your career. Here I am using SQL Server as a backend so this tutorial will be the complete package for you about the web api topic. 
So watch the tutorial and learn about web api.

1) Create new Web API project            00:01:16
2) Create table in sql server                               00:02:42
3) Add New Web API Controller   00:04:32
4) Add Entity Framework      00:05:00
5) Create Post method     00:07:29
6) Create Get method for all products  00:11:35    
7) Create Get method for single product  00:13:23    
8) Create Put method       00:15:47
9) Create Delete method     00:18:21
10) Summary       00:21:00

Subscribe: https://www.youtube.com/c/ManojDeshwal/featured 

#webapi #sqlserver  

Web API Tutorial | Web API Crud Operations with Entity Framework & SQL Server

SQL Managed Instance link - Connecting SQL Server 2016 to Azure

Connecting SQL Server 2016 to Azure - SQL Managed Instance link | Data Exposed

Link feature for Managed Instance is a new feature providing a hybrid connection between SQL Server 2016 (Enterprise, Developer and Standard editions) hosted anywhere and the fully managed PaaS service Azure SQL Managed Instance, providing unprecedented hybrid flexibility and database mobility. With an approach that uses near real-time data replication to Azure using Always On technology, you can offload workloads to read-only secondaries on Azure to take advantage of a fully managed database platform, performance, and scale. The link can be operated for as long as you need it – months and years at a time, empowering you to get all the modern benefits of Azure today without migrating to the cloud. On your modernization journey, when and if you are ready to migrate to the cloud, the link de-risks your migration experience allowing you to validate your workloads in Azure prior to migrating with a seamless and instant experience, and at your own pace. In this episode of Data Exposed with Dani Ljepava and Anna Hoffman, you'll dive deeper into the insights of this new feature.

0:00 Introduction
1:33 What is link and why use it?
2:20 Link feature overview
3:13 Demo: Establishing the link
7:45 Link is based on distributed Availability Groups
9:25 Link networking requirements
11:05 Minimum downtime migrations
11:46 Workload consolidation and de-consolidation in Azure
12:50 Demo: Minimum downtime migration to Azure
15:14 Getting started

✔️ Resources:
Link feature for Azure SQL Managed Instance (preview): https://aka.ms/mi-link-2016 

#azure #sql #sqlserver #database

SQL Managed Instance link - Connecting SQL Server 2016 to Azure
Louis Jones

Louis Jones

1652847033

MySQL vs Postgresql vs Microsoft SQL Server | Which Option is Best?

MySQL vs Postgresql vs Microsoft SQL Server | Which Option is Best?

There are a lot of different SQL options to choose from, but which one is right for you? In this video we take a high level look at the similarities and differences between these types of SQL.

0:00 Intro
1:14 High Level Comparison
3:44 MySQL User Interface and Features
5:43 Microsoft SQL Server User Interface and Features
10:08 Postgresql User Interface and Features
12:55 Overall Thoughts and Recommendations


SQL Server, PostgreSQL, MySQL... what's the difference? Where do I start?

In this tutorial, you'll learn some of the basic differences between SQL dialects and where you should begin.

A relational database is a set of tables (datasets with rows and columns) that contain information relating to other tables in the database.

The diagram below contains information about columns in two tables in an example relational database. Both tables contain columns named customer_id, which establishes a relationship between the tables. As the company grows and records thousands (or millions) of orders, storing data in separate tables helps optimize for space and reduce the size of the database.

SQL, or Structured Query Language, is the standard language for interacting with relational databases. With SQL, you can query, or ask questions of, the data in a relational database. Working with SQL and relational databases is an invaluable skill set for a data analyst, data engineer, or a data scientist.

If you have started looking for ways to learn SQL, you may have noticed the many different dialects of SQL available to learn with some clear (and less clear) distinctions between the different dialects. So where do you begin? Which version of SQL is most helpful to you if you haven’t used it before? In this article, we will focus on four of the most popular database management systems -- PostgreSQL, MySQL, SQLite, and SQL Server -- and their versions of SQL syntax.

The graph below from Stack Overflow Trends provides a sense of how often each of these platforms is discussed -- each line represents the percentage of all Stack Overflow questions about each version of SQL.

MySQL has consistently been the most popular version of SQL in Stack Overflow questions. Second in line is Microsoft SQL Server (including T-SQL, the name of Microsoft’s dialect of SQL), which remains a consistently more popular tag than PostgreSQL and SQLite. This means that if you have a question specific to one of these systems, you’re more likely to find that someone already asked your question.

What’s the difference?

PostgreSQL, MySQL, and SQLite use very similar syntax, with some notable differences highlighted below. Microsoft SQL Server has the greatest contrast in SQL syntax, as well as a wide variety of functions not available in other platforms. The table below highlights some examples of basic differences between SQL platforms.

 SQL ServerMySQLPostgreSQLSQLite
SELECT ...Select [col1], [col2]SELECT col1, col2SELECT col1, col2SELECT col1, col2
Data from tables is case sensitive?Yes WHERE name = ‘John’ Or WHERE name = ‘john’ are not the sameNo WHERE name = ‘John’ Or WHERE name = ‘john’ are the sameYes WHERE name = ‘John’ Or WHERE name = ‘john’ are not the sameYes WHERE name = ‘John’ Or WHERE name = ‘john’ are not the same
Using quotation marksname = ‘John’ onlyname = ‘John’ or name = “John”name = ‘John’ onlyname = ‘John’ or name = “John”
Aliases for columns and tablesSELECT AVG(col1)=avg1SELECT AVG(col1) AS avg1SELECT AVG(col1) AS avg1SELECT AVG(col1) AS avg1
Working with datesGETDATE() DATEPART()CURDATE() CURTIME() EXTRACT()CURRENT_DATE() CURRENT_TIME() EXTRACT()DATE(‘now’) strftime()
Window functions i.e., OVER(), PARTITION BY()YesYesYesNo (need to use subqueries instead)

Where do I start?

For students who have little to no experience with SQL and are looking to gain the most broadly applicable skills, I recommend starting with PostgreSQL. Despite the overwhelming popularity of MySQL, PostgreSQL may be a better choice because its syntax most closely conforms to Standard SQL. This means that you can easily translate your skills to other database management systems such as MySQL or SQLite. For example, the query below aggregates data from a database of sales information. It contains a join, an aggregate function, and a filter. This syntax will generate identical results in all three database systems.

Select    
    c.customer_name,
    SUM(p.amount) AS total_sales
FROM customers AS c
    LEFT JOIN purchases AS p
    ON c.customers_id = p.customer_id
WHERE
    c.customer_location = 'USA'
GROUP BY
    c.customer_name;

If you anticipate working with Microsoft SQL Server in your career, I recommend you start by learning T-SQL/Microsoft SQL. SQL Server continues to maintain a sizable market share and is an important database management system in many industries. If you are unsure which of these two is more important for you, I recommend browsing through job openings in your field to determine if there is a preference for a specific database system in specific roles or industries.


MySQL Download: https://www.mysql.com/downloads/ 
MySQL Workbench Download: https://www.mysql.com/products/workbench/ 

Postgresql Download: https://www.postgresql.org/download/ 
Pgadmin 4 Download: https://www.pgadmin.org/download/ 

SQL Server Download: https://www.microsoft.com/en-us/sql-server/sql-server-downloads 
SQL Server Management Studio Download: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 

#mysql #postgresql #microsoft #sqlserver 

 

MySQL vs Postgresql vs Microsoft SQL Server | Which Option is Best?
Coding  Life

Coding Life

1652516983

How to Create ASP.NET Core Web Application With SQL Server Database Connection

Create your first ASP.NET Core Web Application with Database Access. Connect ASP.NET Core Web Application with SQL Server Database. Perform CRUD operations using ASP.NET. Create Read Update Delete data from SQL Server Database using ASP.NET Core Web Application. 

SQL Queries to create the clients table and to insert rows: 
CREATE TABLE clients (
   id INT NOT NULL PRIMARY KEY IDENTITY,
   name VARCHAR (100) NOT NULL,
   email VARCHAR (150) NOT NULL UNIQUE,
   phone VARCHAR(20) NULL,
   address VARCHAR(100) NULL,
   created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

 

INSERT INTO clients (name, email, phone, address)
VALUES
('Bill Gates', 'bill.gates@microsoft.com', '+123456789', 'New York, USA'),
('Elon Musk', 'elon.musk@spacex.com', '+111222333', 'Florida, USA'),
('Will Smith', 'will.smith@gmail.com', '+111333555', 'California, USA'),
('Bob Marley', 'bob@gmail.com', '+111555999', 'Texas, USA'),
('Cristiano Ronaldo', 'cristiano.ronaldo@gmail.com', '+32447788993', 'Manchester, England'),
('Boris Johnson', 'boris.johnson@gmail.com', '+4499778855', 'London, England');
 

Subscribe: https://www.youtube.com/c/BoostMyTool/featured 

#aspdotnet #sqlserver  

How to Create ASP.NET Core Web Application With SQL Server Database Connection
Cleora  Roob

Cleora Roob

1650667980

Create a .NET custom database logging provider with ASP.NET Core's ILogger and ILoggerProvider

One of the great things about .NET is being able to create a custom logging provider.

Creating a logging provider is relatively easy in ASP.NET Core as you can build the functionality around methods that are contained in the ILogger and ILoggerProvider interfaces.

This video will walk you through the steps to create a custom logging provider that will take appsettings.json properties to log to a SQL Server database table.

It will walk you through how to configure and implement database logging through an ASP.NET Core app. There is functionality available to configure error handling on particular assemblies and namespaces.

As we are writing the log in JSON format, we'll show you how to query JSON strings if you're using SQL Server 2017, or above.

► Chapters
0:00 Coming up...
0:11 How to create a custom logging provider
0:54 Code walkthrough
4:24 Code demo
4:40 JSON in SQL Server
5:41 Other custom logging providers

► More information: https://www.roundthecode.com/dotnet/create-custom-database-logging-provider-asp-net-core-ilogger?

#ilogger #aspnetcore #sqlserver

Create a .NET custom database logging provider with ASP.NET Core's ILogger and ILoggerProvider

How to Set Up SQL Server Database with Docker for Beginners

Docker is a product that allows developers to create containers, which are self-contained areas on their computer for running applications. They can be used for databases, which is great if you can't install it normally (e.g. you use a Mac).

This video shows you how to set up an SQL Server database on Docker. It uses an existing SQL Server 2017 image on the Docker Hub website. You can also use SQL Server 2019 - it's also available on Docker Hub.

You'll learn how to download and install Docker, find the right SQL Server image, download the image, run it, and connect to it in Azure Data Studio. You can use SQL Server Management Studio or any other IDE.

The connection details for this image mentioned in the video are below.

Host: Localhost
Port: 1433
Username: sa
Password: whatever you specify in the "docker run" command

Timestamps:
00:00 In this video
00:08 High-level steps
00:30 Download Docker
01:22 Run Docker
02:05 Create Docker Hub account
02:41 Search for SQL Server image on Docker Hub
03:37 Understand the docker run command
04:25 Open Terminal and login to Docker
05:03 Download image using Docker Run (inc set password and container name)
06:29 Check status using docker ps
06:41 Connect using Azure Data Studio
07:24 Enter connection details
08:00 Run simple query
08:43 Stop Docker container

For more database-related content, visit my website at https://www.databasestar.com 

Subscribe: https://www.youtube.com/c/DatabaseStar/featured 

#docker #sqlserver  

How to Set Up SQL Server Database with Docker for Beginners
Toby Rogers

Toby Rogers

1650425558

Nocodb: Turns any MySQL, Postgres, SQLite into a Spreadsheet with REST APIs

NocoDB
The Open Source Airtable Alternative 
Turns any MySQL, PostgreSQL, SQL Server, SQLite & MariaDB into a smart-spreadsheet.

The Open Source Airtable Alternative - works on MySQL, Postgres SQL Server & MariaDB

 

        

Quick try

1-Click Deploy to Heroku

Before doing so, make sure you have a Heroku account. By default, an add-on Heroku Postgres will be used as meta database. You can see the connection string defined in DATABASE_URL by navigating to Heroku App Settings and selecting Config Vars.

NPX

You can run below command if you need an interactive configuration.

npx create-nocodb-app

Node Application

We provide a simple NodeJS Application for getting started.

git clone https://github.com/nocodb/nocodb-seed
cd nocodb-seed
npm install
npm start

Docker

# for SQLite
docker run -d --name nocodb \
-v "$(pwd)"/nocodb:/usr/app/data/ \
-p 8080:8080 \
nocodb/nocodb:latest

# for MySQL
docker run -d --name nocodb-mysql \
-v "$(pwd)"/nocodb:/usr/app/data/ \
-p 8080:8080 \
-e NC_DB="mysql2://host.docker.internal:3306?u=root&p=password&d=d1" \
-e NC_AUTH_JWT_SECRET="569a1821-0a93-45e8-87ab-eb857f20a010" \
nocodb/nocodb:latest

# for PostgreSQL
docker run -d --name nocodb-postgres \
-v "$(pwd)"/nocodb:/usr/app/data/ \
-p 8080:8080 \
-e NC_DB="pg://host.docker.internal:5432?u=root&p=password&d=d1" \
-e NC_AUTH_JWT_SECRET="569a1821-0a93-45e8-87ab-eb857f20a010" \
nocodb/nocodb:latest

# for MSSQL
docker run -d --name nocodb-mssql \
-v "$(pwd)"/nocodb:/usr/app/data/ \
-p 8080:8080 \
-e NC_DB="mssql://host.docker.internal:1433?u=root&p=password&d=d1" \
-e NC_AUTH_JWT_SECRET="569a1821-0a93-45e8-87ab-eb857f20a010" \
nocodb/nocodb:latest

To persist data in docker you can mount volume at /usr/app/data/ since 0.10.6. Otherwise your data will be lost after recreating the container.

If you plan to input some special characters, you may need to change the character set and collation yourself when creating the database. Please check out the examples for MySQL Docker.

Docker Compose

We provide different docker-compose.yml files under this directory. Here are some examples.

git clone https://github.com/nocodb/nocodb
# for MySQL
cd nocodb/docker-compose/mysql
# for PostgreSQL
cd nocodb/docker-compose/pg
# for MSSQL
cd nocodb/docker-compose/mssql
docker-compose up -d

To persist data in docker, you can mount volume at /usr/app/data/ since 0.10.6. Otherwise your data will be lost after recreating the container.

If you plan to input some special characters, you may need to change the character set and collation yourself when creating the database. Please check out the examples for MySQL Docker Compose.

GUI

Access Dashboard using : http://localhost:8080/dashboard

Screenshots

2 
 

1 
 

7 
 

5 
 

6 
 

5 
 

6 
 

11 
 

10 
 

8


 

9

Features

Rich Spreadsheet Interface

  • ⚡  Basic Operations: Create, Read, Update and Delete on Tables, Columns, and Rows
  • ⚡  Fields Operations: Sort, Filter, Hide / Unhide Columns
  • ⚡  Multiple Views Types: Grid (By default), Gallery and Form View
  • ⚡  View Permissions Types: Collaborative Views, & Locked Views
  • ⚡  Share Bases / Views: either Public or Private (with Password Protected)
  • ⚡  Variant Cell Types: ID, LinkToAnotherRecord, Lookup, Rollup, SingleLineText, Attachement, Currency, Formula and etc
  • ⚡  Access Control with Roles : Fine-grained Access Control at different levels
  • ⚡  and more ...

App Store for Workflow Automations

We provide different integrations in three main categories. See App Store for details.

  • ⚡  Chat : Slack, Discord, Mattermost, and etc
  • ⚡  Email : AWS SES, SMTP, MailerSend, and etc
  • ⚡  Storage : AWS S3, Google Cloud Storage, Minio, and etc

Programmatic Access

We provide the following ways to let users to invoke actions in a programmatic way. You can use a token (either JWT or Social Auth) to sign your requests for authorization to NocoDB.

  • ⚡  REST APIs
  • ⚡  NocoDB SDK

Sync Schema

We allow you to sync schema changes if you have made changes outside NocoDB GUI. However, it has to be noted then you will have to bring your own schema migrations for moving from environment to others. See Sync Schema for details.

Audit

We are keeping all the user operation logs under one place. See Audit for details.

Production Setup

By default, SQLite is used for storing meta data. However, you can specify your own database. The connection params for this database can be specified in NC_DB environment variable. Moreover, we also provide the below environment variables for configuration.

Environment variables

Please refer to Environment variables

Development Setup

Cloning the Project

git clone https://github.com/nocodb/nocodb
cd nocodb

Running Backend locally

cd packages/nocodb
npm install
npm run watch:run
# open localhost:8080/dashboard in browser

Running Frontend locally

cd packages/nc-gui
npm install
npm run dev
# open localhost:3000/dashboard in browser

Changes made to code automatically restart.

nocodb/packages/nocodb includes nc-lib-gui which is the built version of nc-gui hosted in npm registry. You can visit localhost:8000/dashboard in browser after starting the backend locally if you just want to modify the backend only.

Running Cypress tests locally

# install dependencies(cypress)
npm install

# run mysql database with required database using docker compose
docker-compose -f ./scripts/docker-compose-cypress.yml up

# Run backend api using following command
npm run start:api

# Run frontend web UI using following command
npm run start:web

# wait until both 3000 and 8080 ports are available
# and run cypress test using following command
npm run cypress:run

# or run following command to run it with GUI
npm run cypress:open

Contributing

Please refer to Contribution Guide.

Why are we building this?

Most internet businesses equip themselves with either spreadsheet or a database to solve their business needs. Spreadsheets are used by a Billion+ humans collaboratively every single day. However, we are way off working at similar speeds on databases which are way more powerful tools when it comes to computing. Attempts to solve this with SaaS offerings has meant horrible access controls, vendor lockin, data lockin, abrupt price changes & most importantly a glass ceiling on what's possible in future.

Our Mission

Our mission is to provide the most powerful no-code interface for databases which is open source to every single internet business in the world. This would not only democratise access to a powerful computing tool but also bring forth a billion+ people who will have radical tinkering-and-building abilities on internet.

Download Details: 
Author: nocodb
Source Code: https://github.com/nocodb/nocodb 
License: AGPL-3.0 License
#mysql #restapi #sqlite #postgresql #sqlserver #database

Nocodb: Turns any MySQL, Postgres, SQLite into a Spreadsheet with REST APIs