SQL Server Dynamic data masking (2016 feature)

SQL Server Dynamic data masking is available in four different formats based on the data types.

Default- Fully masked data as per the data types of the selected fields.

ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'default()')

Email – Using this method we can apply masking on the email data of the users.

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

Custom String – As per the name, this method can be used to mask the starting characters and last characters as per the custom requirement.

ALTER COLUMN [Mobile Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Random – This function is used to replace number columns with random values.

ALTER COLUMN [OrdrerNO] ADD MASKED WITH (FUNCTION = 'random(1, 5)')

SQL Evolution

Common SQL Questions

.Write an SQL Query to find first weekday of the month?

SELECT 

DATENAME(dw, DATEADD(dd,  -DATEPART(dd, GETDATE()) + 1, GETDATE())) 

AS FirstDay

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

A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.

The different types of relationships in DBMS are as follows:

• One-to-One Relationship –  Used when a single row in Table A is related to a single row in Table B.
• One-to-Many Relationship – Used when a single row in Table A is related to many rows in table B.
• Many-to-Many Relationship – Used when many rows in table A can be related to many rows in table B.
• Self -Referencing Relationship – Used when a record in table A is related to record in same table.

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

A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table

.

CREATE FUNCTION samplefunc(@num INT)

RETURNS TABLE

AS

RETURN SELECT * FROM customers WHERE CustId=@num

SELECT * FROM samplefunc(10)

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

Mention the differences between SUBSTR and CHARINDEX in SQL Server.

SUBSTR	CHARINDEX
Used to return a specific portion of the string in a given string	Used to return a character position in a given specified string
Example:SUBSTRING(‘Edureka’,1,4) Output:Edur	Example:CHARINDEX(‘r’,’Edureka’,1) Output:4

SQL Interview Questions

https://www.gangboard.com/blog/sql-server-dba-interview-questions-and-answers

https://www.dbamantra.com/category/sql-server/

https://www.edureka.co/blog/interview-questions/sql-server-interview-questions/

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

https://blog.sqlxdetails.com/checkpoint-vs-lazy-writer/

https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

https://blog.sqlauthority.com/2010/12/13/sql-server-what-is-page-life-expectancy-ple-counter/

http://geekswithblogs.net/HumpreyCogay/archive/2016/02/29/lock-pages-in-memory.aspx

https://sqlbak.com/blog/tail-log-backups/

https://www.sqlshack.com/tail-log-backup-and-restore-in-sql-server/

https://www.databasejournal.com/features/mssql/backup-and-restore-strategies-in-sql-server-part-1.html

https://www.mssqltips.com/sqlservertip/3213/sql-server-analysis-services-perspectives/

https://www.dbamantra.com/sql-server-dba-interview-questions-answers-sql-server-cluster-2/

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/survey-of-initial-areas-in-in-memory-oltp?view=sql-server-2017

https://www.sqlshack.com/sql-server-2014-columnstore-index/

https://www.dbamantra.com/sql-server-dba-interview-questions-answers-database-mirroring-1/

https://www.sqlshack.com/database-table-partitioning-sql-server/

https://www.springpeople.com/blog/performance-tuning-in-sql-server-tips-and-tricks/


SSRS
https://www.edureka.co/blog/interview-questions/power-bi-interview-questions/

https://www.gangboard.com/blog/ssrs-interview-questions/?utm_source=socials&utm_medium=fb&utm_campaign=iqa&utm_term=msbi&utm_content=kiruthika&fbclid=IwAR3k96LoljUqqD4yyR3CKFzBOY-iyHzjRW7iCJTOPxdoEhGZM1l3AOHHmro


SSIS

https://www.gangboard.com/blog/ssis-interview-questions-and-answers/

BCP Command

There are various methods available for bulk data operations.

1. BCP utility
2. BULK INSERT
3. Using OPENROWSET
4. Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information

BCP  DatabaseName.SchemaName.[TableName | ViewName] | Query

     [IN datafile | OUT datafile | queryout datafile | format] 

     [Options]

First enble CMDSHELL

Use Master

GO

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master..xp_cmdshell 'BCP [AdventureWorks2012].[Person].[Address] OUT c:\Person.xls -T -c'