Saturday

SQL Server: Top 10 Queries of SQL Server

Introduction

T-SQL most top queries demonstrating today in this article. T-SQL is most important while dealing with database. MS SQL Server has provided  numerous T-SQL queries to check database performance, data manipulation queries, data definitions queries, roles/security check, transaction level etc.

Database Table with Data

CREATE TABLE [dbo].[tblPersonalDetail](
 [TablePK] [int] IDENTITY(1,1) NOT NULL,
 [First_Name] [nvarchar](100) NULL,
 [Last_Name] [nvarchar](100) NULL,
 [EmailID] [nchar](100) NULL,
 [Address] [nvarchar](200) NULL,
 [CellNo] [int] NULL,
 CONSTRAINT [PK_tblPersonalDetail] PRIMARY KEY CLUSTERED 
(
 [TablePK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblPersonalDetail] ON
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],
[Address], [CellNo]) VALUES (1, N'David', N'Moore', N'david.moore@you.com                                                                                 ', N'new lane drive USA', 456789456)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],
 [Address], [CellNo]) VALUES (2, N'Marry', N'Champ', N'marrychamp@you.com                                                                                  ', N'straight drive, Lane I', 15478945)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],
 [Address], [CellNo]) VALUES (3, N'Allan', N'Lamb', N'allan@you.com                                                                                       ', N'Main Lane, London', 12312313)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID], 
[Address], [CellNo]) VALUES (4, N'Arya', N'Bhatt', N'arya@abc.com                                                                                        ', N'Nalanda University', 12313154)
SET IDENTITY_INSERT [dbo].[tblPersonalDetail] OFF

Table Data View

table data view

SQL Next Record: How to get next record in SQL Server Table

SELECT TOP 1 LEAD(PD.TablePK) OVER (ORDER BY PD.TablePK) NextValue from 
 [tblPersonalDetail] PD where PD.TablePK >=1 order by PD.TablePK asc;
SQL NEXT RECORD

SQL Previous Record: Go to Previous Record in Table using SQL Server

SELECT TOP 1 MAX(TablePK)Previous_Record FROM tblPersonalDetail WHERE TablePK < 3;
SQL Previous Record

SQL Server: How to check Total Rows, Spaced Used, Indexed and unused Size?

EXEC sp_spaceused @updateusage = N'TRUE';
GO
EXEC sp_spaceused N'dbo.tblPersonalDetail';
check sql space used

Highest Salary: How to get 3rd Highest Value using SQL Server

SELECT TOP 1 TablePK from (SELECT DISTINCT TOP 3 TablePK FROM tblPersonalDetail 
 order by TablePK desc) a order by TablePK

SQL Defined Tables: List all user defined tables using SQL Server


SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
INFORMATION SCHEMA TABLES

SQL Columns: List all columns in our database using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
INFORMATION SCHEMA COLUMNS

Other INFORMATION_SCHEMA.ViewName: Here are listing of other Information_Schema metadata which allows you to retrieve rows of specific database from Master database.

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.DOMAINS
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEWS

SQL Date Formats: To see the date formats for your culture using MS SQL Server

EXEC sp_helplanguage

EXEC sp_helplanguage

Fig: Date Formats

XML Showplan: This statement returns showplan in XML format by the plan handle.

USE master;
GO
exec sp_who;
GO
SELECT * FROM sys.dm_exec_requests
XML Showplan

                                     Fig: XML Showplan

Select Last SQL Records: In this query how we can select last records using Top Statement in T-SQL

SELECT Top 4 * FROM dbo.tblPersonalDetail Order By TablePK desc
Select Top Query

Conclusion

In this article, I have demonstrated T-SQL top 10 queries in SQL Server. Which shows data from Master database of the SQL Server and few queries manipulate of specific database. Whenever we are working on top db administrator then we need to fire administrator level queries to tune up our database.

Relevant Reading

Thursday

Entity Framework: Store update insert or delete statement affected an unexpected number of rows (0)

Introduction

I was working on MVC application using Entity Framework, meanwhile I was setting up my code then a new error comes off me "Store update, insert, or delete statement affected an unexpected number of rows". After my deep study, then came up a perfect solution to remove this issue permanently from my solution. I have explained all steps as given below.

Error Description

Store update, insert, or delete statement affected an unexpected number of rows (0).
Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Store update insert or delete statement affected an unexpected number of rows (0)
Fix Entity Framework Error
Steps to Remove Error [Store update, insert, or delete statement affected an unexpected]

Set update mode to add, it was earlier set to modified state using entity framework. If table primary key greater than zero then it will modified otherwise it will add a new record in database table.

Wrong Statement

I had written given below wrong statement to add a new fresh record into my database table record, it was wrongly added state to Modified.
_context.Entry(model).State = EntityState.Modified;

Corrected Statement

Now, I corrected this statement, I just checked that primary key contains a value means a record need to update otherwise primary key value 0 means my database table needed to add a new fresh record in to my database table record.

if (model.TablePK > 0)
{
   _context.Entry(model).State = EntityState.Modified;
}
else
{
   _context.Entry(model).State = EntityState.Add;
}

Conclusion

While we coding seriously in our app then suddenly can trap up in few unexpected issues i.e. Store update, insert, or delete statement affected an unexpected. I have provided complete to recover unexpected issue. Hope it will help to all who eventually got in this issue i.e. Store update, insert, or delete statement affected an unexpected.

Suggested Reading

Monday

Database Repair Tool: How to Repair MS SQL Database Using Stellar Phoenix SQL Database Repair Tool

Introduction

Stellar Phoenix SQL Database repair tool is able to repair corrupt .mdf and .ndf files. Few important features of this tool are:

  • The tool supports MS SQL Server 2016, 2014, 2012, 2008 and all the lower versions. 
  • It has the ability of self-reconnect after interruption on the repairing process.
  • Restores the compressed (Row compressed, page compressed) data.
  • Recovery of clustered and non-clustered indexes. Etc

Step 1: Download and install from here Stellar Phoenix SQL Database Repair 

The main interface of the tool looks like this. Select the corrupt database (.mdf) file.

Stellar Phoenix SQL Database Repair

User can also use Search the .mdf file in the specific drive by Search in Folder option. To include the deleted records, click on the check box.

Include Deleted Records

Step 2: Open the corrupt .mdf to start Repair Process.

Repair Process
Step 3: Click on the Repair button to start the repair process as shown in the figure below:

start repair
Step 4: After completion of the repair process, User become able to see the preview of the data.

preview of the data

Step 5: User have option to save the log report in .XLS, .HTML and CSV formats. Click on the Save Log option which is present in the top ribbon then save the log file.

Save Log

Step 6: To register the tool, click on the Registration button then select the appropriate option.

Registration

Conclusion

Steller Phoenix SQL Database Repair helps to repair all SQL Server database (SQL 2008, 2012, 2014, 2016 & lower  versionbs) with great perfection. This tools is enabled with great capability to recover corrupted SQL database of all versions up to date.

Relevant Reading

Wednesday

Jquery: How to allow only alphabets in textbox using Jquery

Introduction

This example will explain how to allow only characters [a/z] in text box using Jquery. It will restrict to write you special characters, functions keys and other keys as well.

Main Features

It will allow backspaces and white spaces while entering the text in input text box.
If you like to write capital letter then need to on Caps Lock of your keyboard.
Jquery Textbox Alphabets

Mostly When It required

It is mostly required when letting to user enter his name, Company Name, Organization name etc.

Summary

Really, Jquery is still playing vital role to make our front end more interactive and user friendly.
Already demonstrated above all steps that how to allow only characters [a/z] in text box using Jquery.

Working Example

Relevant Reading

Friday

Jquery Datepicker: How to Implement Jquery Datepicker

Introduction

Jquery is now a day’s very powerful tool to make your UI more robust and interactive.
I am also explaining about Jquery Fiddle/example from last few days along with C#, MVC, ASP.Net examples. In this article, I am going to explain Jquery Interactive Calendar with its flexible properties. Here, you can change Your Calendar Year, Month instantly rather than tying into input box, moreover added UI CSS to make look and feel more sober.
Jquery Datepicker

Jquery MVC: Upload Image using jquery MVC

Introduction

Image Upload is very important work in our web application. It is most commonly required while registering profile someone with profile image, creating album etc. I have explained in this article, how to upload image in MVC application (Razor Engine) using Jquery and preview image immediately. Image Uploading is beautiful task of your application whenever search someone image, if profile image is blur or heavy image then it will increase your process time, blur image will lead to bad impact in visitor mind means reduce traffic towards website or profile. I have bring up a dashing working example.

Saturday

Jquery: Capitalize the First Letter of String Using Jquery

Introduction

Today, I am explaining one more working example of Jquery. I have explained how we can capitalize first letter of string using Jquery. It is very common requirements while we are typing sentence in input box. I have used JSfiddle to run here fully working example of Jquery.
Jquery Capitalize First Letter