January 2021 – Run SQL Server BLOG

28th January 202127th January 2021

Oversizing a column in a table – finding varchar(max) and text in MsSQL

It is very commmon when a table have one or more to wide columns.
Sometimes developers choose not supported columns, for example, text type in MS SQL database.
I always looking for text, ntext, varchar(max) and nvarchar(max) :

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
 -- ORDINAL_POSITION,
  IS_NULLABLE,DATA_TYPE,  CHARACTER_SET_NAME, COLLATION_NAME
  ,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
DATA_TYPE= 'text' 
or DATA_TYPE='ntext' 
or  (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1) 
or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)
order by TABLE_NAME,DATA_TYPE
-- and TABLE_NAME ='table

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,

-- ORDINAL_POSITION,

IS_NULLABLE,DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME

,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE

DATA_TYPE= 'text'

or DATA_TYPE='ntext'

or (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1)

or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)

order by TABLE_NAME,DATA_TYPE

-- and TABLE_NAME ='table

After it, I check the (n)varchar and (n)char columns.

I am finding the longest string.

-- varchar type
SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:
SELECT max(datalength([oszlopnev])) FROM [tablename]

-- varchar type

SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:

SELECT max(datalength([oszlopnev])) FROM [tablename]

27th January 202127th January 2021

Index fragmentation with table row count and index creation and modification date

You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.

USE UserDB     -- with modifydate -order by rowcount
GO

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
 ,create_date
 ,modify_date
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY record_count desc, avg_fragmentation_in_percent DESC

USE UserDB -- with modifydate -order by rowcount

SELECT s.[name] +'.'+t.[name] AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

If you want to see for all database you can replace the

DB_ID()

with

NULL

in the script.

16th January 20217th February 2021

Python and MS SQL connection

Install ODBC driver for MS SQL>
pip install pyodbc
Downloading pyodbc-4.0.30-cp39-cp39-win_amd64.whl (67 kB)
|████████████████████████████████| 67 kB 745 kB/s
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.30

Python query:

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:192.168.0.13' 
database = 'AdventureWorks2017' 
username = 'dbuser' 
password = 'strongpassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute('SELECT  TOP(5) [FirstName]  ,[MiddleName]  ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')
for row in cursor:
    print(row)

import pyodbc

# Some other example server values are

# server = 'localhost\sqlexpress' # for a named instance

# server = 'myserver,port' # to specify an alternate port

server = 'tcp:192.168.0.13'

database = 'AdventureWorks2017'

username = 'dbuser'

password = 'strongpassword'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = conn.cursor()

cursor.execute('SELECT TOP(5) [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')

for row in cursor:

print(row)