BigDataNSQL @bigdatansql - Tumblr Blog

In BigData & SQL there are 1000+ articles and books - but I thought in this AI generation it is not much of use and I closed down the site for few weeks. However, the requests say otherwise and forced me to bring back the site online. Explore, learn and enjoy.

Alternative of DBCC CHECKDB Repair Allow Data Loss

DBCC CHECKDB is executed on the SQL Server database to check the physical and logical integrity of the database’s integral components. These objects include tables, indexes, keys, stored procedures, triggers, etc. DBCC CHECKDB also checks the structure of all the database tables and indexes. In case any of these checks fail while executing DBCC, you will get an error message that looks similar to…

#Recover Data Pages in SQL Server #Repair Corrupt SQL Database #Repair Data Loss in SQL Server #REPAIR_ALLOW_DATA_LOSS in SQL Server

How to Resolve SQL Server 'Database cannot be Opened' Issue due to MDF Corruption?

Master Database File (MDF) is the primary database file in MS SQL Server that stores all the data, including views, tables, stored procedures, foreign keys, and primary keys. Sometimes, while opening the MDF file, you may face an issue where the SQL Server database cannot be opened and encounter an error, like the one given below: SQL Server error 5171: MDF is not a primary database file Unable…

#Database Cannot be opened error #Database Cannot Be Opened in SQL Server #MDF Corruption in SQL Server #StellarInfo SQL Server MDF Repair

USE of GO in SQL Server

GO is required at the specified point if you modify an already-existing stored procedure, if you have an execution statement open in the same query window, and if you intend to execute both statements simultaneously. If not, the execution statement will b

GO is interpreted by SQL Server utilities as a signal to deliver the current batch of Transact-SQL statements to a SQL Server instance. All statements entered since the last GO, or since the beginning of the ad hoc session or script if this is the first GO, make up the current batch of statements. To put it simply, SQL commands are grouped into batches and delivered to the server collectively…

View On WordPress

#CREATE VIEW must be the first statement in a query batch #CREATE/ALTER PROCEDURE statement must be the first statement in a query batch #CREATE FUNCTION statement must be the first statement in a query batch #GO in SQL Server #GO Usage in SQL Server #Why to use GO in SQL Server

Can we convert TIMESTAMP to date time?

The short answer is, NO.

The short answer is, NO. Let’s consider the following example. USE testDB GO CREATE TABLE tbOrders ( iOrderID INT IDENTITY(1,1) PRIMARY KEY, dtOrderDate DATETIME, dcPaidAmount Decimal(9,2), TIMESTAMP ); INSERT INTO tbOrders(dtOrderDate, dcPaidAmount) VALUES (‘2023-11-01’, 2750), (‘2023-11-02’, 3950), (‘2023-11-03’, 7000); Now retrieve the data and see the results. SELECT * FROM tbOrders The…

View On WordPress

#Can we convert TIMESTAMP to date time?#TIMESTAMP NO DEFAULT VALUE #TIMESTAMP rowversion

Stored Procedure - RAISERROR

Here's an example of how to make a user-defined error message and raise it in the event that something goes wrong.

Here’s an example of how to make a user-defined error message and raise it in the event that something goes wrong. — Creating the procedure CREATE PROCEDURE uspUpdateValue AS BEGIN TRY DECLARE @ErrorMsg VARCHAR(100) DECLARE @ErrorMessage VARCHAR(100) DECLARE @Severity INT DECLARE @State INT SET @Severity = 10 SET @State = 1 SET @ErrorMessage = ‘An error occured while updating Table Order…

View On WordPress

#Error Handling in SQL #RAISERROR in SQL Server #User defined error message in SQL Server

MySQLDump Backup and Restore All Databases at once

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another serv

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another server. The mysqldump command can also generate output in CSV, other delimited text, or XML format. Although it appears to be a little executable…

View On WordPress

#MySQL backup and restore on from one server to another server in one go #MySQL backup and restore same time #mysqldump #mysqldump all databases #mysqldump all databases separate files #mysqldump backup all databases into separate files #mysqldump options #separate database files while using mysqldump

Assign a serial number to result set - MySQL

Concerned parties may occasionally request that a serial number be displayed alongside the result set when a report is being generated. If the table lacks an auto-increment column or the result-set is lacking a unique identifier, you can assign one dynami

Concerned parties may occasionally request that a serial number be displayed alongside the result set when a report is being generated. If the table lacks an auto-increment column or the result-set is lacking a unique identifier, you can assign one dynamically using the method outlined below. — Sample datasetCREATE TABLE tbCustomer ( CustName VARCHAR(45) NOT NULL, CustPhone VARCHAR(45)…

View On WordPress

#Add serial Number to MySQL output #Generate serial number in mysql query #Generate serial number to mysql result set #How to display Serial Numbers with mysql query result #How to Number Rows in an SQL Result Set #MySQL ROW_NUMBER() Function

Generate SubOrder ID to Orders MySQL

To differentiate between OrderIDs that are identical, it is suggested to include a SubOrderID as an additional field with a serial number suffix. For example, in the event that the OrderID is 100123 and is repeated twice, the SubOrderID values in the additional column should be 1001231 and 1001232. — Sample dataset CREATE TABLE tbOrders( iOrderID INT, OrderDate DATETIME); INSERT INTO tbOrders…

View On WordPress

#Calculated Column in MySQL #CONCAT in MySQL #Derived Column in MySQL #Generate serial number to duplicate entries in MySQL

Anonymity with Random Values in MySQL

Organisations can use data anonymization as a strategy to abide by strict data privacy rules that require the protection of personally identifiable information (PII), such as date of births, emails, identification details, monetary information, contact in

Organisations can use data anonymization as a strategy to abide by strict data privacy rules that require the protection of personally identifiable information (PII), such as date of births, emails, identification details, monetary information, contact information, and information from health reports. The following will assist in making the data anonymous. 1) Updating random values for text…

View On WordPress

#Anonymity with Random Values in MySQL #Data Anonymization #Data Masking in MySQL #random values for emails #random values for monetary fields #random values for text fields

SQL Sample Datasets and Assignments

Here is a compilation of earlier blog posts that were relevant to Sample datasets, Sample projects, and Sample assignments. For quick reference only.

Here is a compilation of earlier blog posts that were relevant to Sample datasets, Sample projects, and Sample assignments. For quick reference only. Sample Datasets to Practice Complex Data Types I’ve written a number of articles in this blog so far about complex data types (in Apache Hive). I frequently used the JPG format to display the datasets and results. The link to all the sample data…

View On WordPress

MySQL Administration

The articles about MySQL administration that have previously been posted on this blog are listed below. For quick reference only.

The articles about MySQL administration that have previously been posted on this blog are listed below. For quick reference only. Install MySQL in Mac This tutorial will assist you in using Brew to install MySQL on a Mac. user@hostname ~ % brew install mysql The above command will help in installing the latest MySQL version available. It will take a few minutes to complete the process. Once…

View On WordPress

Hadoop Default Ports

Computers are able to distinguish between various traffic types with the use of ports. A default open port is a port number that is preconfigured to accept internet connections and packets from specific services using protocols like the User Datagram Prot

Computers are able to distinguish between various traffic types with the use of ports. A default open port is a port number that is preconfigured to accept internet connections and packets from specific services using protocols like the User Datagram Protocol (UDP), Stream Control Transmission Protocol (SCTP), Datagram Congestion Control Protocol (DCCP), or Transmission Control Protocol (TCP). In…

View On WordPress

#DataNode Ports #Hadoop changed default ports #Hadoop Default Ports #NameNode Ports

Metadata Information from SYS tables - SQL Server

The underlying tables which store the metadata for a certain database are called system tables. A system table called sys tables is used to keep track of data about the tables in a database. A record is created in the sys for each table or object that is

The underlying tables which store the metadata for a certain database are called system tables. A system table called sys tables is used to keep track of data about the tables in a database. A record is created in the sys for each table or object that is added to the database. These system tables belong to a unique class of tables that the SQL Server Engine uses to hold data on the configurations…

View On WordPress

#Get the schemas tables columns and indexes #Metadata in SQL Server #Metadata information from SYS tables #Metadata Information in SQL Server #Metadata Queries in SQL Server #System Tables in SQL Server

Get the information about the backup and restore operations

The msdb database keeps a complete record of every SQL Server backup and restore procedure performed on a server instance. The data for the SQL Server Agent jobs, job history, operators, and alarms is stored in this database. It includes information abou

The msdb database keeps a complete record of every SQL Server backup and restore procedure performed on a server instance. The data for the SQL Server Agent jobs, job history, operators, and alarms is stored in this database. It includes information about log shipping settings, backup and restore history, and database activity. Additionally, it contains the data required to run Database Mail. In…

View On WordPress

#Backup and Restore History in SQL Server #Backup History SQL Server #SQL Server Backup and Restore Information

SQL Server Stored Procedures, Triggers and Cursors

The articles about SQL Server stored procedures, triggers, and cursors that have previously been posted on this blog are collected here. For quick reference only.

The articles about SQL Server stored procedures, triggers, and cursors that have previously been posted on this blog are collected here. For quick reference only. Stored Procedures in SQL Server Introduction: A collection of SQL statements is referred to as a stored procedure in SQL and is stored together in a database. It can execute one or more DML operations on the database and return value,…

View On WordPress

#SQL Server Cursors #SQL Server Stored Procedures #SQL Server Triggers

List of Scheduled Jobs - SQL Server

Scheduled administrative tasks, referred to in SQL Server as jobs, are carried out by SQL Server Agent which is a crucial component of SQL Server and is a Microsoft Windows service. Using SQL Server Agent, we can schedule and automate database and server management activities. The following query will assist in determining which jobs were generated and scheduled for automatic execution by SQL…

View On WordPress

#List of Scheduled Jobs in SQL Server #Query to get the list of scheduled jobs in SQL Server #SQL Server Agent #SQL Server Agent Jobs

Trending Blogs

Recently Viewed Blogs

BigDataNSQL