SQL Server — Core Concepts with examples

 

• Data Definition Language (DDL): CREATE, ALTER, DROP (tables, views, procedures, triggers).

• Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.

• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT.

• Indexes: clustered vs non-clustered, covering indexes, filtered indexes.

• Stored Procedures: encapsulate logic, enforce controlled access.

• Functions: scalar, table-valued, system functions (e.g., LEN, LEFT, RIGHT, REPLICATE).

• Triggers: automatic actions on INSERT, UPDATE, DELETE.

• Transactions: BEGIN TRAN, COMMIT, ROLLBACK for consistency.

• Security: roles, permissions, encryption, dynamic data masking.

Advanced SQL Server Features

• Views: virtual tables for abstraction and security.

• CTEs (Common Table Expressions): recursive queries, cleaner logic.

• Window Functions: ROW_NUMBER(), RANK(), LEAD(), LAG().

• Partitioning: split large tables for performance.

• Stored Procedure Best Practices: validation, error handling, logging.

• Dynamic SQL: build queries at runtime (with caution).

• Performance Tuning: query plans, indexes, statistics, caching.

• High Availability: replication, mirroring, AlwaysOn availability groups.

API Security (Angular + .NET + SQL Server)

• Frontend Validation (Angular): user experience only, not security.

• Backend Validation (.NET): enforce rules, limits, sanitization.

• Rate Limiting: prevent brute force attacks.

• Authentication: JWT, OAuth2, ASP.NET Identity.

• Authorization: role-based, claims-based.

• SQL Security: parameterized queries, stored procedures, deny direct table access.

• Audit Logging: track who did what, when.

Angular applications, below is a comprehensive TypeScript reference

 

Angular This covers the vast majority of TypeScript features used in modern Angular and enterprise web applications. Mastering these topics will prepare you for most day-to-day development as well as senior-level TypeScript interviews.

1. Variables

let name: string = "Vijay";
const age: number = 25;
var city = "Mumbai";

---

2. Data Types

let str: string = "Hello";
let num: number = 100;
let isActive: boolean = true;
let data: any = {};
let value: unknown;
let empty: null = null;
let undef: undefined = undefined;
let big: bigint = 100n;
let sym: symbol = Symbol("id");

---

3. Arrays

let numbers: number[] = [1,2,3];

let names: Array<string> = ["A","B"];

numbers.push(4);
numbers.pop();
numbers.shift();
numbers.unshift(0);

numbers.includes(2);
numbers.indexOf(2);

numbers.sort();
numbers.reverse();

---

4. Objects

const emp = {
    id:1,
    name:"John"
};

console.log(emp.name);

---

5. Interface

interface Employee{
    id:number;
    name:string;
}

const emp:Employee={
    id:1,
    name:"John"
}

---

6. Type Alias

type User={
    id:number;
    name:string;
}

---

7. Enum

enum Status{
    Pending,
    Success,
    Failed
}

let status=Status.Success;

---

8. Functions

Normal

function add(a:number,b:number){
   return a+b;
}

Arrow

const add=(a:number,b:number)=>a+b;

Optional Parameter

function greet(name?:string){}

Default Parameter

function greet(name="Guest"){}

Rest Parameter

function total(...nums:number[]){
    return nums.reduce((a,b)=>a+b);
}

---

9. Callback

function process(fn:Function){
   fn();
}

---

10. Anonymous Function

const test=function(){
    console.log("Hello");
}

---

11. Classes

class Employee{

    constructor(
      public id:number,
      public name:string
    ){}

    display(){
        console.log(this.name);
    }
}

---

12. Inheritance

class Person{}

class Employee extends Person{}

---

13. Access Modifiers

public

private

protected

readonly

Example

class Test{

   public a=10;

   private b=20;

   protected c=30;

   readonly d=40;
}

---

14. Static

class MathUtil{

   static PI=3.14;
}

---

15. Abstract Class

abstract class Animal{

   abstract sound():void;
}

---

16. Getter Setter

class User{

   private _name="";

   get name(){
      return this._name;
   }

   set name(value:string){
      this._name=value;
   }
}

---

17. Generic

function identity<T>(value:T):T{
    return value;
}

---

18. Promise

const promise=new Promise((resolve)=>{
    resolve("Success");
});

---

19. Async Await

async function load(){

   const data=await fetch(url);

}

---

20. Optional Chaining

user?.address?.city;

---

21. Nullish Coalescing

name ?? "Unknown"

---

22. Destructuring

const {name,age}=user;

const [a,b]=numbers;

---

23. Spread Operator

const arr2=[...arr1];

const obj2={...obj1};

---

24. Rest Operator

const [...values]=arr;

---

25. Type Assertion

const input=document.getElementById("txt") as HTMLInputElement;

---

26. Union

let value:string|number;

---

27. Intersection

type A={a:number};

type B={b:number};

type C=A&B;

---

28. Literal Type

let status:"success"|"error";

---

29. keyof

type Keys=keyof Employee;

---

30. typeof

typeof user;

---

31. instanceof

if(obj instanceof Employee){}

---

32. in Operator

"name" in user;

---

33. for...of

for(const item of array){}

---

34. for...in

for(const key in obj){}

---

35. switch

switch(status){

case 1:

break;

default:

}

---

36. try catch

try{

}catch(error){

}

---

37. Map

const map=new Map();

map.set("id",1);

map.get("id");

map.has("id");

map.delete("id");

map.clear();

---

38. Set

const set=new Set();

set.add(1);

set.has(1);

set.delete(1);

---

39. WeakMap

const wm=new WeakMap();

---

40. WeakSet

const ws=new WeakSet();

---

41. Date

const date=new Date();

date.getFullYear();

date.getMonth();

date.getDate();

date.toISOString();

---

42. JSON

JSON.stringify(obj);

JSON.parse(json);

---

43. Math

Math.max();

Math.min();

Math.floor();

Math.ceil();

Math.random();

Math.round();

Math.abs();

Math.sqrt();

Math.pow();

---

44. String Methods

trim()

trimStart()

trimEnd()

includes()

startsWith()

endsWith()

substring()

slice()

replace()

replaceAll()

split()

concat()

repeat()

toUpperCase()

toLowerCase()

charAt()

indexOf()

lastIndexOf()

padStart()

padEnd()

match()

search()

localeCompare()

---

45. Number Methods

toFixed()

toPrecision()

toString()

parseInt()

parseFloat()

Number()

isNaN()

isFinite()

---

46. Array Methods (Most Used)

map()

filter()

find()

findIndex()

findLast()

findLastIndex()

some()

every()

reduce()

reduceRight()

flat()

flatMap()

join()

slice()

splice()

fill()

copyWithin()

entries()

keys()

values()

at()

push()

pop()

shift()

unshift()

sort()

reverse()

includes()

indexOf()

lastIndexOf()

concat()

from()

of()

Example

users
.filter(x=>x.active)
.map(x=>x.name)
.sort();

---

47. Object Methods

Object.keys()

Object.values()

Object.entries()

Object.assign()

Object.freeze()

Object.seal()

Object.hasOwn()

Object.create()

Object.fromEntries()

---

48. Useful Utility Types

Partial<T>

Required<T>

Readonly<T>

Record<K,T>

Pick<T,K>

Omit<T,K>

Exclude<T,U>

Extract<T,U>

NonNullable<T>

ReturnType<T>

Parameters<T>

Awaited<T>

Example

type UpdateEmployee=Partial<Employee>;

---

49. Decorators (Angular)

@Component()

@Injectable()

@Pipe()

@Directive()

@Input()

@Output()

@HostListener()

@HostBinding()

@ViewChild()

@ViewChildren()

@ContentChild()

@ContentChildren()

---

50. Common Browser APIs

setTimeout()

setInterval()

clearTimeout()

clearInterval()

fetch()

localStorage

sessionStorage

navigator

history

location

URL

URLSearchParams

AbortController

---

Enterprise TypeScript features you should master

• Classes and inheritance

• Interfaces and type aliases

• Generics

• Utility types (Partial, Pick, Omit, Record)

• Async/await and Promises

• Optional chaining (?.) and nullish coalescing (??)

• Array methods (map, filter, reduce, find, some, every)

• Object methods (keys, entries, assign)

• ES Modules (import/export)

• Decorators (Angular)

• Enums and literal types

• Type narrowing (typeof, instanceof, in)

• Type assertions and custom type guards

• Maps, Sets, WeakMaps, and WeakSet