Download free Microsoft EBooks Including: Windows 10, Windows 8.1, Windows 8, Windows 7, Office 2013, Office 365, SharePoint 2013, Dynamics CRM, PowerShell, Exchange Server, Lync 2013, System Center, Azure, Cloud, SQL Server

https://blogs.msdn.microsoft.com/mssmallbiz/2015/07/07/im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-windows-8-1-windows-8-windows-7-office-2013-office-365-sharepoint-2013-dynamics-crm-powershell-exchange-se/

 

 

Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

 

Check Fragmentation using sys.dm_db_index_physical_stats

 

SELECT 'Limited' as Mode,page_count, compressed_page_count as cm_page_count,index_type_desc
,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'LIMITED');

SELECT 'Sampled' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'SAMPLED');

SELECT 'Detailed' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'DETAILED');

Fragmentation output

Mode: Limited

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Doesn’t look into the actual pages.

Mode: Sampled

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages) out-of-order extents in the leaf pages of a heap
  • Scans a sample of leaf pages (10 % approximately)  it may randomly these pages.

Mode: Detailed

  • Does what limited mode normally do but scans every page in the index including the intermediate and root pages.
  • Check the logical fragmentation at every level of Index (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Scans the entire index.

Detailed mode is more accurate than any other mode because it scans every page of the index but this  might create some performance bottle neck. It depends on where each of this mode can be used if an index with high fanout (index key length) which is directly proportional to the number of levels , it makes sense to check the fragmentation at intermediate levels using detailed mode at least once a week as part of Index Maint plan.

 

Terms : 

  •  Out of Order pages : An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
  • Out-of-order extent: An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

 

B-Tree Index Structure:

Heap:

Heap

Non Clustered or Clustered Index

Non Clustered or Clustered Index

 

 

Aurdino based led dimmer

Goal: Control the brightness of the led using Aurdino and a potentiometer.

We achieve this by reading output potentiometer using analogRead with output range between 0 to 1023 and convert this into analog signal between 0 to 255, to do this we use a map function to transform the values 0 to 1023 to 0 to 255 and write to a pwm pin no 5.

IMG_7144

Capture

 

Capture2

 

Snap

void setup() {
// put your setup code here, to run once:
Serial.begin(9600);
pinMode(5,OUTPUT);
pinMode(A0,INPUT);
}

void loop() {
// put your main code here, to run repeatedly:
int sensorValue = analogRead(A0);
int outputValue = map(sensorValue, 0, 1023, 0, 255);
//Write Output value to pwm pin 5
analogWrite(5,outputValue);
// print out the value you read:
Serial.println(sensorValue);
delay(1);
}

 

 

 

 

 

Powershell To Deploy Sql scripts in a Directory

Source Code

[CmdletBinding()]
Param(
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Instance")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetInst,
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Source")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetDb,
   [Parameter(Mandatory=$True,ValueFromPipeline=$true)]
   [string]$Targetdir

)

## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Write-host $TargetInst $Targetdb $targetdir
$sourcedbs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $TargetInst 
$sourcedbs.ConnectionContext.StatementTimeout = 0
$db = $sourcedbs.Databases[$TargetDB]
$files = Get-ChildItem -Path "$Targetdir"  -Recurse  | Where-Object{$_.Name -Like "*.sql"} | select FullName
Foreach($file in $files) 
{
$query= Get-Content $file.FullName | Out-String ; 
	try
	{
		$db.ExecuteNonQuery($query)
		Write-Host $file.FullName "Query Finished"     
    }
      catch [Exception]
 	{

		Write-Host $_.Exception.GetType().FullName, $_.Exception.Message
	}
}

 

How to use it

.\ExecuteSqlscripts.ps1 ServerName DatabaseName Directory

Note: it does a recursive check if there any folders it gets them all as well.

 

How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

 

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance
    •  User will be removed  — @fix = 0
      • If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
    •  or A login will be created  — @fix = 1

 


DECLARE @t TABLE (

id INT IDENTITY(1, 1)

,username SYSNAME

,usersid VARBINARY(85)

);

DECLARE @username SYSNAME = ;

DECLARE @loopid INT = 0D

ECLARE @DeleteCmd NVARCHAR(1000) = 

DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed 

— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (

username

,usersid

)

EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0

BEGIN

SET @username = (

SELECT username

FROM @t

WHERE id = @loopid

)

IF EXISTS (

SELECT 1

FROM master.dbo.syslogins

WHERE NAME = @username

)

BEGIN

EXEC sp_change_users_login @Action = ‘update_one’

,@UserNamePattern = @username

,@LoginName = @username;

PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘

END

ELSE

BEGIN

IF @fix = 0

BEGIN

SELECT @DeleteCmd = @DeleteCmd + (

CASE

WHEN s.NAME IS NULL

THEN 

ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’

END

) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’

FROM sys.database_principals dp

LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id

WHERE type IN (

‘s’

,‘G’

,‘U’

)

AND dp.principal_id > 4

AND dp.NAME = @username

PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘

END

ELSE

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’

,@username

,NULL

,‘Auto_Fix_Pass’;

PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘

END

END

SET @loopid = @loopid  1;

ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd

— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;

Column information of all the tables in a database (SqlServer2008 R2, MySQL, Oracle)

 

Different RDBMS provides system views with information about all the columns and their definitions in the database tables 

 

SQLServer :  SYS.COLUMNS (Microsoft) 

                   INFORMATION_SCHEMA.COLUMNS (ANSI SQL)

Oracle       : ALL_TAB_COLUMNS (ANSI SQL)

My Sql       : INFORMATION_SCHEMA.COLUMNS (ANSI SQL)

 

Microsoft SQL server’s INFORMATION_SCHEMA.COLUMNS is equivalent to ALL_TAB_COLUMNS in oracle and INFORMATION_SCHEMA.COLUMNS in Mysql 

ALL_TAB_COLUMNS in oracle  is equivalent to  Microsoft SQL server’s INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.COLUMNS in Mysql 

INFORMATION_SCHEMA.COLUMNS in Mysql is equivalent to  ALL_TAB_COLUMNS in oracle  Microsoft SQL server’s INFORMATION_SCHEMA.COLUMNS and 

 

SQL Server 

select *  from INFORMATION_SCHEMA.COLUMNS

Oracle

select * from ALL_TAB_COLUMNS

 

My SQL 

select *  from INFORMATION_SCHEMA.COLUMNS