Monday, April 10, 2017

SQL Server Free eBooks, Softwares, Tutorials and Websites

When we start working as developer or learning, we often face this issue. Where is the best tutorials, books and other learning material? Sure, google can help you but it is difficult to keep track of all the information provided by the google. While working for more than 7 year, I have identified some resource which are really great and would like to share with you. Best part of this all are free and trustworthy.

First thing first, we need a computer with all necessary software because it doesn’t matter how much you study until you do hands on practice. Here is the list of software and official download links.

  1. SQL Server 2016 Developer Edition – it has all the necessary features and free from Microsoft. You can use this for learning purpose, please read the user agreement for all the details.
https://www.microsoft.com/en-us/sql-server/application-development


  1. Visual Studio Community Edition
https://www.visualstudio.com/downloads/


  1. SQL Server Data Tools in Visual Studio 2015
https://msdn.microsoft.com/en-us/mt186501.aspx?f=255&MSPPError=-2147217396


I would recommend you the following installation sequence as -
  1. SQL Server
  2. Visual Studio
  3. SQL Server Data Tool
Once installation is done, you need sample database to start with. Here is the link to download sample database from Microsoft –
AdventureWorks Databases and Scripts for SQL Server 2016

https://www.microsoft.com/en-us/download/details.aspx?id=49502

 

Wide World Importers sample database v1.0


https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0



Top websites to master SQL Server –
  1. Official Microsoft website - https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
  2. https://www.mssqltips.com/
  3. https://www.w3schools.com/sql/default.asp
  4. https://www.simple-talk.com/sql/
  5. http://stackoverflow.com/

Free eBooks –
Defensive Database Programming
By Alex Kuznetsov
Download Link - http://www.sqlservercentral.com/articles/books/69647/


Mastering SQL Server Profiler

Download Link -  http://www.sqlservercentral.com/articles/books/65797/

SQL Server Execution Plans


Performance Tuning with SQL Server Dynamic Management Views


Inside the SQL Server Query Optimizer


SQL Server Execution Plans, Second Edition



SQL Server Concurrency: Locking, Blocking and Row Versioning


List of all the books –


Best online Tutorials -


Stairway to T-SQL: Beyond The Basics


Stairway to T-SQL DML


Stairway to SQL Server Indexes


Stairway to Data


Stairway to Advanced T-SQL


List of all Stairway tutorials


















    
Posted by at
Labels: ,

Tuesday, March 28, 2017

Alter Datatype of All the Columns In Database

Requirement: Recently we got the information from management that our project will be getting the customers from China, so our application should support the Unicode characters. Now we support only English language, to support all the languages we decided to change all the char, varchar and text datatypes to nchar, nvarchar and ntext. Process to achieve this is below –

  1. Get the list of Columns which are using datatypes char, varchar and text
  2. Fetch the details like table name, datatype, length etc.
  3. Generate the Alter statement(Dynamic SQL)
  4. Execute  Dynamic SQL
  5. Capture the status (success or failure) with error details
  6. Analyze the error message and manually update the datatypes for failed records
This process has reduced the manual work by 80% (approximately) in our case.

We found that some Alter statements failed due to Default constraints or Column being used in Index. We dropped the constraints, Alter the datatype and recreated them. Since the failed percentage was very less, so we did not tried to automate them.


/*####################################################################*/

BEGIN

/*Create the table to collect the column details and status*/
IF OBJECT_ID (N'ColumnDataTypeChange', N'U') IS NOT NULL
BEGIN
Truncate Table ColumnDataTypeChange
END
Else
BEGIN
Create Table ColumnDataTypeChange
(
ID int identity(1,1) Primary Key,
TableName nVarchar(1000),
ColumnName nVarchar(1000),
DataType nVarchar(1000),
ColumnSize int,
isUpdated bit,
ErrorMsg  nVarchar(1000),
SQL_Script nVarchar(2000)
)
END
/*Fetch the details of columns*/
Insert into ColumnDataTypeChange
SELECT distinct O.name as 'TableName',C.name as 'ColumnName',t.name as 'DataType',C.max_length as 'ColumnSize',
 0 as isUpdated, NULL as ErrorMsg,NULL as SQL_Script
FROM   
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
Inner Join sys.objects O on O.object_id=C.object_id and O.type='U'
WHERE T.name='varchar' or T.name='char' or T.name='text'

Declare @TotalRec int
Declare @Count int
Declare @TableName nVarchar(1000)
Declare @ColumnName nVarchar(1000)
Declare @DataType nVarchar(1000)
Declare @ColumnSize int
Declare @SQL nVarchar(1000)
Declare @ErrorMsg nVarchar(1000)

SET @Count=1
SET @TotalRec=(SELECT COUNT(*) FROM ColumnDataTypeChange T)
/*Generate Alter statement and execute them*/
While @Count<=@TotalRec
BEGIN

BEGIN TRY
SET @TableName =(SELECT TOP 1 T.TableName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnName =(SELECT TOP 1 T.ColumnName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @DataType =(SELECT TOP 1 T.DataType FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnSize =(SELECT TOP 1 T.ColumnSize FROM ColumnDataTypeChange T WHERE T.ID=@Count )

SET @SQL='ALTER TABLE dbo.'+@TableName+' ALTER COLUMN '+'['+@ColumnName+']'+' '+'n'+@DataType+'('+CONVERT(nvarchar,@ColumnSize)+')'

EXEC(@SQL)

      Update ColumnDataTypeChange
      SET isUpdated=1
      WHERE ID=@Count

END TRY

BEGIN CATCH 
    /*Capture the error details in case of failure*/ 
      SET @ErrorMsg=(SELECT ERROR_MESSAGE() AS ErrorMessage)

      Update ColumnDataTypeChange
      SET isUpdated=0
      , ErrorMsg= @ErrorMsg
      ,SQL_Script=@SQL
      WHERE ID=@Count
    
END CATCH;


SET @Count=@Count+1
END-- While ENDs

END

/*####################################################################*/ 

Please share your thoughts and feel free to suggest if you think there is a better way to do this.



Posted by at

Sunday, February 19, 2017

SQL Server Tips and Tricks

In this article I will be listing the tips and tricks to work like a Pro. 
So let's get started –

Important keyboard shortcuts for Sql Server Management Studio.

CTRL+N – Opens new query window
CTRL+SHIFT+N – Open pop window to create new project
CTRL+SHIFT+TAB – Cycle through the previous MDI child windows
END – Move the cursor to the end of the line
HOME – Move the cursor to the beginning of the line
CTRL+HOME – Move the cursor to the beginning of the document
CTRL+END – Move the cursor to the end of the document
PAGE UP – Move the cursor up one screen
PAGE DOWN - Move the cursor down one screen           
CTRL+ RIGHT ARROW – Move cursor to one word towards right
CTRL+ LEFT ARROW – Move cursor to one word towards left
CTRL+PAGE UP – Moves the cursor to the top of the document
CTRL+PAGE DOWN – Moves the cursor to the bottom of the document
CTRL+UP ARROW - Scroll text up one line
CTRL+DOWN ARROW - Scroll text down one line
CTRL+Z - Reverse the last editing action
CTRL+Y - Restore the previously undone edit
F5 - Run the selected portion of the query editor or the entire query editor if nothing is selected

If you want to see the whole list , please visit the below link from Microsoft website.

https://msdn.microsoft.com/en-in/library/ms174205.aspx
---------------------------------------------------------------------
Important System tables and queries

Get the list of databases on the server -

select * from Sys.databases

Get the list of objects (tables, constraints, Views, Triggers etc.) in your database and basic details -

Select Obj.name,Obj.type,Obj.type_desc,Obj.create_date,Obj.modify_date
from Sys.objects Obj


Get the list of all columns in a table or list of all tables and columns –

Select Obj.name,Obj.type_desc,Col.name from Sys.objects Obj
Inner Join Sys.columns Col on Col.object_id=Obj.object_id
Where Obj.name='Employee'
Order by Obj.name,Col.name


Select  Obj.name,Obj.type_desc,Col.name from Sys.objects Obj
Inner Join Sys.columns Col on Col.object_id=Obj.object_id
Order by Obj.name,Col.name

Get the list of table and indexes on them -

Select Obj.name,Obj.type_desc,Ind.name from Sys.objects Obj
Inner Join Sys.indexes Ind on Ind.object_id=Obj.object_id
Where Obj.name='Employee'
Order by Obj.name,Ind.name



Select  Obj.name,Obj.type_desc,Ind.name from Sys.objects Obj
Inner Join Sys.indexes Ind on Ind.object_id=Obj.object_id
Order by Obj.name,Ind.name


Get the list of user defined views -

select Vw.name,Vw.schema_id,Vw.type_desc,Vw.create_date,Vw.modify_date
from Sys.all_views Vw where Vw.is_ms_shipped=0


----------------------------------------------------------------------------

sp_helptext - this could lot of your time, instead of browsing through the list of stored procedure, you can use this. Here is the syntax


sp_helptext ‘uspGetBillOfMaterials

https://msdn.microsoft.com/en-IN/library/ms176112.aspx


sp_helpindex – this provide the details about the index on the table like index name, description and key.


sp_helpindex '[HumanResources].[Employee]'


index_name
index_description
index_keys
AK_Employee_LoginID
nonclustered, unique located on PRIMARY
LoginID
AK_Employee_NationalIDNumber
nonclustered, unique located on PRIMARY
NationalIDNumber
AK_Employee_rowguid
nonclustered, unique located on PRIMARY
rowguid
IX_Employee_OrganizationLevel_OrganizationNode
nonclustered located on PRIMARY
OrganizationLevel, OrganizationNode
IX_Employee_OrganizationNode
nonclustered located on PRIMARY
OrganizationNode
PK_Employee_BusinessEntityID
clustered, unique, primary key located on PRIMARY
BusinessEntityID


sp_helpstats – this one provides statistics details about columns and indexes on the table.

sp_helpstats '[Sales].[SalesOrderDetail]'

statistics_name
statistics_keys
_WA_Sys_00000006_44CA3770
SpecialOfferID


sp_helptrigger  - It provides the information about DML triggers on the given table.
sp_helptrigger  '[Sales].[SalesOrderDetail]'

trigger_name
trigger_owner
isupdate
isdelete
isinsert
isafter
isinsteadof
trigger_schema
iduSalesOrderDetail
dbo
1
1
1
1
0
Sales

Posted by at

Monday, February 6, 2017

Part 2 - Detecting and Monitoring Memory Pressure Using DMV

I will be writing this article in four parts because this topic is huge and the amount of information can be overwhelming for readers and for me also. So we will be exploring it step by step.

1.                    DBCC MemoryStatus
2.                    Using DMV
3.                    Alert and perfmon counters

As you may know, Dynamic Management Views were introduced in SQL Server 2005 and since then these are important source of information for database health status. DMV can be used to monitor SQL Server overall health, finding the root cause of performance bottlenecks and tuning database performance.In this article we will be concentrating on DMVs important for finding and resolving Memory issues. Since there are many DMV for memory status and utilization, so we will be concentrating on the most useful ones. You can explore other DMV by simply writing a select statement and going through information provided by them.

sys.dm_os_sys_info

This provides the information about the system and SQL Server resources and consumption. It gives the overall data about physical and virtual memory, CPU count etc which can help us to analyze the overall memory and CPU utilization. When I ran the select query against sys.dm_os_sys_info, I got the following result for my environment.

select
T.affinity_type,
T.affinity_type_desc,
T.committed_kb,
T.committed_target_kb,
T.cpu_count,
T.cpu_ticks,
T.deadlock_monitor_serial_number,
T.hyperthread_ratio,
T.max_workers_count,
T.ms_ticks,
T.os_error_mode,
T.os_priority_class,
T.os_quantum,
T.physical_memory_kb,
T.process_kernel_time_ms,
T.process_user_time_ms,
T.sqlserver_start_time,
T.sqlserver_start_time_ms_ticks
T.virtual_machine_type,
T.virtual_machine_type_desc,
T.virtual_memory_kb,
T.visible_target_kb
from sys.dm_os_sys_info T





cpu_count- it shows the number of logical CPU in the system.
cpu_ticks- it shows the current tick count for the CPU and this is read from processor's RDTSC counter.
ms_ticks- it shows the number of milliseconds , since the system restarted.
physical_memory_kb- it shows the total physical memory in the system.
virtual_memory_kb- it show shows the total virtual address space available for user process.
max_workers_count- it shows maximum number of worker threads that can be created.
sqlserver_start_time- it shows the last time SQL Server was restarted.


sys.dm_os_memory_cache_counters
It provides the snapshot of the cache and this information can be used to understand the health of cache in SQL Server instance. When I ran the below script on my SQL Server instance, I got the following output. I am showing only first few rows here.

select
T.cache_address,
T.entries_count,
T.entries_in_use_count,
T.name,
T.pages_in_use_kb,
T.pages_kb,
T.type
from sys.dm_os_memory_cache_counters T




 sys.dm_os_memory_nodes: Returns information about the memory nodes. 

select
T.memory_node_id,
T.virtual_address_space_reserved_kb,
T.virtual_address_space_committed_kb,
T.locked_page_allocations_kb,
T.pages_kb,
T.shared_memory_committed_kb,
T.shared_memory_reserved_kb,
T.cpu_affinity_mask,
T.online_scheduler_mask,
T.processor_group,
T.foreign_committed_kb
from sys.dm_os_memory_nodes T



 sys.dm_os_memory_cache_entries:  Returns original and current cost of any cache entry.
select
T.cache_address,
T.name,
T.type,
T.entry_address,
T.entry_data_address,
T.in_use_count,
T.is_dirty,
T.disk_ios_count,
T.context_switches_count,
T.original_cost,
T.current_cost,
T.memory_object_address,
T.pages_kb,
T.entry_data,
T.pool_id,
T.time_to_generate,
T.use_count,
T.average_time_between_uses,
T.time_since_last_use,
T.probability_of_reuse,
T.value
from  sys.dm_os_memory_cache_entries T




Object Catalog Views

The following object catalog views are used specifically with In-Memory OLTP.
Ø   sys.hash_indexes
Ø   sys.memory_optimized_tables_internal_attributes


Internal DMVs

There are additional DMVs that are intended for internal use only, and for which we provide no direct documentation. In the area of memory-optimized tables, undocumented DMVs include the following:
Ø  sys.dm_xtp_threads
Ø  sys.dm_xtp_transaction_recent_rows





















Posted by at
Subscribe to: Posts (Atom)