SQL join: where clause vs. on clause

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
    AND Orders.ID = 12345


a. WHERE clause: After joining. Records will be filtered after join has taken place.

b. ON clause - Before joining. Records (from right table) will be filtered before joining

JavaScript — What’s the difference between Null & Undefined?

null is an assigned value. It means nothing.
undefined means a variable has been declared but not defined yet.
null is an object. undefined is of type undefined.
null !== undefined but null == undefined.

Differences between Response.End() and Response.Flush()

Response.Flush

Forces all currently buffered output to be sent to the client. The Flush method can be called multiple times during request processing.

Response.End

Sends all currently buffered output to the client, stops execution of the page, and raises the EndRequest event.

You should try using this code if you are not doing any processing on the page after Response.Write and want to stop processing the page.

    context.HttpContext.Response.Clear();

    context.HttpContext.Response.Write(htmlString);              

    context.HttpContext.Response.Flush(); // send all buffered output to client 

    context.HttpContext.Response.End(); // response.end would work fine now.

Database configuration failed: Column Type does not allow DBNull Value

"Configuration wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull Value". We need to allow nulls for the 'Type' column in SQL management studio for the User table.


Configuration Wizard ends with the following error:

Configuration Wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull.Value"

CAUSE
Nulls for the 'Type' column for the ReportDefinitions table were not allowed in SQL Management Studio.

RESOLUTION
Run the following SQL query against the Current database:

update User set type = '' where type = NULL

for example, ‘0000-00-00’ works well with ‘Zero-date in NOT NULL Columns’ setting), or set destination column to NULL so you could process null data after the migration is complete.

update  User  set RENEWAL_DATE= '2000-01-01' where  RENEWAL_DATE = '0000-00-00'

update  User  set THE_TIME= '2000-01-01 00:00:00' where  THE_TIME= '0000-00-00 00:00:00'

Notes:

1. The '' is 2 single quotes one after the other and not a single double quote. The 2 single quotes back to back designates the query to enter a blank value in the field. If you use a double quote, the query will fail.
2. Make sure to backup your database before making direct changes to the database

SQL Server Row Count for all Tables in a Database

SQL server Syntax :

SELECT
     sOBJ.name AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
 
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO


Output : -

Get record counts for all tables in MySQL database

mySql  Syntax:


SELECT  table_rows,table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'SchemaName' and table_rows<>0; --  Schemaname (Databasename)



output

Change Schema Name Of Table In SQL

I want to change schema name of table "Employees"in Database. In the current table Employees database schema name is "Schemaconverted" I want to change it to dbo.

Example:

FROM

Schemaconverted.Employees
TO

dbo.Employees


Syntax

ALTER SCHEMA dbo
    TRANSFER Schemaconverted.Employees

syntax  for All Base tables

use TestDB
go
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'Schemaconverted'
  set @newschema = 'dbo'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)
 end