get partitioned by partition schema in database

SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
t.name AS [Table Name],
i.name AS [Index Name],
s.name AS [Partition Scheme], *
FROM sys.indexes i
INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
INNER JOIN sys.tables t ON i.object_id = t.object_id




Every table in SQL Server has a single partition. If you run above script it will list only those tables which are partitioned by partition scheme and have more than one partitions.

version controlling

Auto Align SSIS diagram in dataflow

trim and change date time if in numeric format ssis and sql

trim ssis


LEN(TRIM(my_numeric_value)) == 0 ? "0.00" : TRIM(my_numeric_value)



change date time format


Eq :
input : 20190731
output: 2019-07-31 00:00:00.000

SUBSTRING([FILE-DTE],1,4) + "-" + SUBSTRING([FILE-DTE],5,2) + "-" + SUBSTRING([FILE-DTE],7,2)



change datetime and  numeric format to varchar

'IN1035_' +CAST(col1 AS nvarchar) + '_' + 
FORMAT([LoadDate],'yyyyMMddHHmmss')
AS BT_ID


using CASE:
CASE
WHEN [Code] LIKE '9%' THEN  [aaDate]
WHEN [Code] LIKE '0%' THEN  DATEADD(d,-1,[aaDate])

END AS [PostingDate]

ELT process

What’s ETL again?

When designing the ETL process it’s good to think about the three fundamental things it needs to do:

• Extract data from the external data sources such as line-of-business systems, CRM systems, relational databases, web services, and SharePoint lists.
• Transform the data. This includes cleansing the data and converting it to a OLAP-friendly data model. The OLAP-friendly data model traditionally consists of dimension and fact tables in a star or snowflake schema and closely maps SSAS’s dimensional model (SSAS stands for SQL Server Analyses Services ).
• Load the data so that it can be quickly accessed by querying tools such as reports. In practice this implies processing SSAS cubes.

Staging :

There’s a quick and easy way to create staging tables from within SSIS. When you add a destination data source to a data flow, connect it to incoming data and then edit the destination’s properties, you will be asked to select a destination table. At that point you also have the option of creating a new table. Doing so will create a database table with all the right columns.

get newly inserted identity column value sql

Table X has as its primary key the column Y which is defined as  int IDENTITY(1,1). After inserting a row into table X, how would you SELECT the newly inserted value of column Y?

Answer:      SELECT IDENT_CURRENT (‘X’) AS Current_Identity;

3.            Table R contains columns ProductID AND OrderDate, amongst others, and contains many rows of data? How would you return just rows 20 to 30 of the data (when ordered by OrderDate and Product ID).

Answer:

SELECT * FROM R order by Product ID, OrderDate  desc LIMIT 19,11

SQL Practice 1 - ResultSets , Object ID

SELECT OBJECT_ID('[ErrorLog]')
SELECT OBJECT_name(277576027)
SELECT OBJECT_NAME(object_id), SCHEMA_NAME(schema_id), * FROM sys.tables


sys_objects
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15


all types in SQL

SQL Lessons sys.tables, objectIID

sys.tables

sys.tables is a system table and is used for maintaining information on tables in a database. For every table added to the database, a record is created in the sys.tables table. There is only one record for each table and it contains information such as table name, object id of table, created date, modified date, etc. Object ID is unique


Select name AS TableName, object_id AS ObjectID
From sys.tables
From sys.tables
–– where name = '<TABLENAME>'
–– Uncomment above line and add <Table Name> to fetch details for particular table



sys.columns
sys.columns is a system table and is used for maintaining information on columns in a database. For every column added in a database, a record is created in the sys.columns table. There is only one record for each column and it contains the following information:
Name: The name of the column. This is unique within the table object.
Object_id: object_id is unique identifier for the table in which the column exists. We will use this column to join sys.columns with sys.tables in order to fetch columns in different tables.
Column_id: ID of the column. This is unique within the table object./li>
user_type_id: System code for column data type
max_length: Maximum length (in bytes) of the column.
is_nullable: 1 if column is nullable.





Add Data Type Name

The next step is to replace Data Type ID with Data Type Name. This can be done by joining the above query with sys.types table.
About sys.types

sys.types is a system table and is used for maintaining information on column data types in a database. This table contains one row for each data type and includes the following information:
Name: The name of the column. This is unique within the table object.
user_type_id: System code for column data type. This is unique for this table and is used for joining with sys.columns table.
max_length: Maximum length (in bytes) of the column.


reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014



Null Validation:
USE AdventureWorks2012; GO IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL DROP TABLE dbo.AWBuildVersion; GO

GAC Location

to open GAC from windows run folder, type in run command

C:\windows\assembly\GAC_MSIL

to open from explorer
C:\windows\assembly

CTE with full outer join example

With CountryCTE(CountryName,Export)
As (SELECT C.Country,ISNULL(sum(O.sumorders),0) as export
FROM [companies] C
full outer JOIN
(
    SELECT seller,sum(value) AS sumorders
    FROM  [dbo].[trades]
    GROUP BY seller   
)O ON C.[name] = O.[seller]
GROUP BY C.Country),
 MyCountryCTE(CountryName,Import)
 As
 (SELECT C.Country,ISNULL(sum(O.sumorders),0) as import
FROM [companies] C
full outer JOIN
(
    SELECT buyer,sum(value) AS sumorders
    FROM  [dbo].[trades]
    GROUP BY buyer   
)O ON C.[name] = O.[buyer]
GROUP BY C.Country)
select CountryCTE.CountryName,Export,Import from CountryCTE full outer join MyCountryCTE on CountryCTE.CountryName=MyCountryCTE.CountryName
order by CountryCTE.CountryName

Command to truncate a SQL Server log file?