viernes, 16 de agosto de 2024

Ejemplo SP + JOB para traslado de tabla/Históricos

Para generar un Job en SQL Server 2019 que traslade los registros más antiguos de la tabla persona a una tabla espejo histórica, manteniendo solo los 10,000 registros más recientes en la tabla principal, puedes seguir los siguientes pasos:

Crear la tabla histórica:

 
CREATE TABLE persona_historica (
    nombre NVARCHAR(255),
    edad INT,
    email NVARCHAR(255),
    identity INT PRIMARY KEY
);

Crear un procedimiento almacenado para realizar la transferencia: Este procedimiento moverá los registros más antiguos a la tabla histórica y luego los eliminará de la tabla principal.

 
CREATE PROCEDURE sp_MoverRegistrosAntiguos
AS
BEGIN
    -- Mover los registros más antiguos a la tabla histórica
    INSERT INTO persona_historica (nombre, edad, email, identity)
    SELECT nombre, edad, email, identity
    FROM persona
    WHERE identity <= (SELECT MIN(identity) 
                       FROM (SELECT TOP 10000 identity 
                             FROM persona 
                             ORDER BY identity DESC) AS t);
    
    -- Eliminar los registros movidos de la tabla principal
    DELETE FROM persona
    WHERE identity <= (SELECT MIN(identity) 
                       FROM (SELECT TOP 10000 identity 
                             FROM persona 
                             ORDER BY identity DESC) AS t);
END;

Crear un Job en SQL Server Agent: Puedes crear un Job en SQL Server Management Studio (SSMS) que ejecute el procedimiento almacenado una vez por semana.
- Abre SQL Server Management Studio (SSMS).
- Navega a SQL Server Agent > Jobs.
- Haz clic derecho en Jobs y selecciona New Job....
- En la pestaña General, dale un nombre al Job, por ejemplo, MoverRegistrosAntiguos.
- En la pestaña Steps, haz clic en New... para agregar un nuevo paso.
  - En Type, selecciona Transact-SQL script (T-SQL).
  - En Command, ingresa la llamada al procedimiento almacenado:
```
sql
EXEC sp_MoverRegistrosAntiguos;
```
  - Haz clic en OK para guardar el paso.
- En la pestaña Schedule, haz clic en New... para crear un nuevo horario.
  - Configura el Job para que corra una vez por semana en el día y hora que prefieras.
- Haz clic en OK para guardar el Job.

Este Job ahora se ejecutará automáticamente una vez por semana, manteniendo solo los 10,000 registros más recientes en la tabla persona y trasladando los demás a la tabla persona_historica.

miércoles, 14 de agosto de 2024

Ejemplo Azure Service Bus, N:1, N:N

El patrón arquitectónico Broker es utilizado para la implementación de sistemas de software distribuidos con componentes desacoplados.

Funciona como un intermediario entre el cliente y el servidor encargado de realizar la coordinación de mensajes, solicitudes, respuestas etc.

La arquitectura broker tiene como objetivo poner un intermediario entre los clientes y el servidor o múltiples de estos, tiene el objetivo de gestionar el envío de información o la ejecución de tareas, al tener esta arquitectura permite a una aplicion tener funciones desacopladas y descentralizadas lo que facilita la escalabilidad y la seguridad del sistema informático.

static async Task Main(string[] args)

{

// Reemplaza con tu cadena de conexión de Service Bus

string connectionString = " ";

string topicName = "topic_poc_01";

string subscription = "subscription_poc_02";

// Crear un cliente de Service Bus

ServiceBusClient client = new ServiceBusClient(connectionString);

try

{

// Enviar un mensaje

await SendMessageAsync(client, topicName, "Hola desde Service Bus");

// Recibir y eliminar un mensaje

await ReceiveAndDeleteMessageAsync(client, topicName, subscription);

}

finally

{

// Cerrar el cliente

await client.DisposeAsync();

}

static async Task SendMessageAsync(ServiceBusClient client, string topicName, string messageBody)

{

// Crear un emisor para el tópico

ServiceBusSender sender = client.CreateSender(topicName);

// Crear un mensaje

//ServiceBusMessage message = new ServiceBusMessage(messageBody);

// Crear un mensaje con la propiedad personalizada

ServiceBusMessage message = new ServiceBusMessage(messageBody);

message.ApplicationProperties.Add("tipo", "orden");

try

{

// Enviar el mensaje

await sender.SendMessageAsync(message);

Console.WriteLine("Mensaje enviado correctamente.");

}

finally

{

// Cerrar el emisor

await sender.DisposeAsync();

}

static async Task ReceiveAndDeleteMessageAsync(ServiceBusClient client, string topicName, string subscription)

{

// Crear un receptor para el tópico

ServiceBusReceiver receiver = client.CreateReceiver(topicName, subscription);

try

{

// Recibir un mensaje

ServiceBusReceivedMessage receivedMessage = await receiver.ReceiveMessageAsync();

if (receivedMessage != null)

{

Console.WriteLine("Mensaje recibido: " + receivedMessage.Body.ToString());

await receiver.CompleteMessageAsync(receivedMessage);

Console.WriteLine("Mensaje eliminado correctamente.");

}

else

{

Console.WriteLine("No se encontraron mensajes.");

}

catch (Exception ex) {

Console.WriteLine(ex.Message);

}

finally

{

// Cerrar el receptor

await receiver.DisposeAsync();

}

miércoles, 12 de julio de 2023

Reiniciar (forzado) VM en Azure

# Definir los detalles de la máquina virtual

$resourceGroupName = "GroupName"
$vmName = "Name"

# Reiniciar la máquina virtual
Restart-AzVM -ResourceGroupName $resourceGroupName -Name $vmName -NoWait Me gusta8:53

martes, 31 de enero de 2023

Función en Javascript para mascara de moneda

La función toma un valor de tipo string y lo formatea como una moneda en colones utilizando la API Intl.NumberFormat(). La función también convierte el valor de string en un número flotante antes de formatearlo para asegurarse de que se formatee correctamente como una moneda.

function applyCurrencyMask(value) {

const formatter = new Intl.NumberFormat('es-CR', {

style: 'currency',

currency: 'CRC',

minimumFractionDigits: 2

});

return formatter.format(parseFloat(value));

}

function formatMoney(number, decPlaces, decSep, thouSep) {
    decPlaces = isNaN(decPlaces = Math.abs(decPlaces)) ? 2 : decPlaces,
    decSep = typeof decSep === "undefined" ? "." : decSep;
    thouSep = typeof thouSep === "undefined" ? "," : thouSep;
    var sign = number < 0 ? "-" : "";
    var i = String(parseInt(number = Math.abs(Number(number) || 0).toFixed(decPlaces)));
    var j = (j = i.length) > 3 ? j % 3 : 0;

    return sign +
        (j ? i.substr(0, j) + thouSep : "") +
        i.substr(j).replace(/(\decSep{3})(?=\decSep)/g, "$1" + thouSep) +
        (decPlaces ? decSep + Math.abs(number - i).toFixed(decPlaces).slice(2) : "");
}

miércoles, 14 de septiembre de 2022

75 Important queries in SQL Server every developer should know

http://www.codingfusion.com/Post/75-Important-queries-in-SQL-Server-every-developer-should-know

75 Important queries in SQL Server every developer should know

In this article we will learn about some of the mostly used SQL Server queries every developer should know. These queries can be asked you as an Interview Question or they are handy for you in your day to day tasks.

1. Create Table:

In this SQL Server query we will learn How to create table in SQL Server.

CREATE TABLE TableName
(
   Id INT,
   Name Nvarchar(500),
   Age INT 
)   

2. Create table with primary key:

In this query we will Create SQL Server table with primary key.

CREATE TABLE TableName
(
   Id INT PRIMARY KEY,
   Name Nvarchar(500),
   Age INT 
)

3. Create table with primary key and Identity Column:

CREATE TABLE TableName
(
   Id INT  IDENTITY(1,1) PRIMARY KEY,
   Name Nvarchar(500),
   Age INT 
)

4. Insert values into SQL Server table:

In this SQL Server query we will learn about How to insert values into Sql Server table.

1	`INSERT INTO TableName (Name,Age) VALUES ('Max',30);`

5. Insert multiple rows in a single query:

In this Sql query we will learn How to Insert Multiple Rows in a Single SQL Query. This query is very important and generally asked in .Net Interview questions.

INSERT INTO TableName (Name,Age) 
VALUES ('Max',30),('John',28),('Jack',31);

6. Update query:

Update single record:

In this Sql query we will update single record from a table.

1	`UPDATE TableName SET NAME='Max Payne'` `WHERE Id=1`

Update all records:

In this Sql query we will update all records within a table.

1	`UPDATE TableName SET AGE=31`

7. Delete query:

Delete single record:

In this Sql query we will delete single record from a table.

1	`DELETE FROM TableName WHERE Id=1`

Delete multiple records:

In this Sql query we will delete all records from a table.

1	`DELETE FROM TableName`

8. Select:

Select all columns from a tables:

In this Sql query we will select all columns from a table.

1	`SELECT * FROM TableName`

Select specific columns:

In this Sql query we will select specific columns from a table.

1	`SELECT columnName1,ColumnName2 from TableName`

9. Create View:

A view is a virtual table created based on the result generated by SQL statement. Fields in a view are directly related to one of more tables from database.

CREATE VIEW view_name AS SELECT Id,Name,Age
FROM TableName

Usage:

1	`select * From view_name`

10. Create Stored procedure:

In this query we will learn about How to create stored procedure in Sql Server.

CREATE PROCEDURE getInfoFromTable
AS
SELECT * FROM TableName

11. Get all column names from table:

In this query we will learn about How to get all column names from Sql Server table.

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

Get column names with data type:

select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

12. Search column name from database:

In this query we will learn about How to search any column name from Sql Server database. This query will look out for matching column names in database.

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%columnNameToSearch%'       

13. Get all user created tables:

In this query we will learn about How to get names of all user created tables in Sql Server.

1	`SELECT NAME FROM sys.objects WHERE TYPE='U'`

14. Get all view names:

In this query we will learn about How to get names of all views in Sql Server.

1	`SELECT * FROM sys.views`

15. Get all stored procedure names:

In this query we will learn about How to get names of all stored procedure in Sql Server.

1	`SELECT * FROM sys.procedures`

16. Counting rows for all tables at once:

In this query we will learn about How to fetch the row count for all tables in a SQL SERVER database. This query is very usefull when we want to know how many records are present in tables.

SELECT 
[TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U'
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC        

17. Get Comma Separated List of all columns in table:

In this query we will learn about How to get Comma Separated List of all columns in table.

Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ',' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
    And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME='TableName'

18. Generate ADO .net parameters:

In this query we will learn about How to Generate ADO .net SqlCommand parameters in Sql Server. Fellows who have worked on asp .net webforms and ADO .net must know the pain of generating parameters for SqlCommand object for large tables. I have written this query to save my time and generate parameters on the fly. This is one of my favourate SQL Query.

SELECT 'cmd.Parameters.AddWithValue("@'+COLUMN_NAME+'", '+COLUMN_NAME+');'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName';   

19. Retrieve List of All Database names:

In this query we will learn about How to get list of all database names in Sql Server.

1	`SELECT Name FROM dbo.sysdatabases`

1	`EXEC sp_databases`

20. Check default Language of SQL Server:

In this query we will learn about How to Check default Language of SQL Server.

1	`select @@language AS DefaultLanguage`

21. Check server name:

In this query we will learn about How to Check SQL Server instance name.

1	`select @@SERVERNAME AS ServerName`

22. Add columns to existing table:

In this query we will learn about How to add new column to existing SQL Server table.

Syntax

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE}

Usage

ALTER TABLE TableName 
ADD Email NVARCHAR(50)

23. Remove column from a table:

In this query we will learn about How to remove column from SQL Server Table.

ALTER TABLE TableName 
DROP COLUMN Email

24. Check column having NULL values only from a table:

In this query we will learn about How to check if any column contains only NULL values in SQL Server Table.

DECLARE @col varchar(255), @cmd varchar(max)
 
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'TableName'
 
OPEN getinfo
 
FETCH NEXT FROM getinfo into @col
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM TableName WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)
 
    FETCH NEXT FROM getinfo into @col
END
 
CLOSE getinfo
DEALLOCATE getinfo

25. Get list of tables without primary key:

In this query we will learn about How to get list of tables without primary key in SQL Server.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

26. Get list of tables without having identity column:

In this query we will learn about How to get list of tables without having identity column in SQL Server.

SELECT  
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
WHERE
Table_NAME NOT IN  
(  
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
INNER  
JOIN sys.identity_columns ic  
on  
(c.COLUMN_NAME=ic.NAME))  
AND  
TABLE_TYPE ='BASE TABLE' 

27. Get month names with month numbers:

In this query we will learn about How to get all month names with month number in SQL Server. I have used this query to bind my dropdownlist with month name and month number.

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
SELECT DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS [MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS MonthNumber
FROM months
ORDER BY Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE()))
;

28. Get name of current month:

In this query we will learn about How to get name of current month in SQL Server.

1	`select DATENAME(MONTH, GETDATE()) AS CurrentMonth`

29. Get name of day:

In this query we will learn about How to get name of day in SQL Server.

1	`select DATENAME(WEEKDAY, GETDATE()) AS TodayIs`

30. Get first date of current month:

In this query we will learn about How to first date of current month in SQL Server.

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate; 

31. Get last date of current month:

In this query we will learn about How to last date of current month in SQL Server.

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) LastDate; 

32. Get first date of previous month:

In this query we will learn about How to first date of previous month in SQL Server.

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) FirstDayOfPreviousMonth 

33. Get last date of previous month:

In this query we will learn about How to last date of previous month in SQL Server.

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastDayOfPreviousMonth  

34. Get first date of current year:

In this query we will learn about How to first date of current year in SQL Server.

1	`SELECT dateadd(year,datediff(year,0,getdate()),0) FirstDateOfYear`

35. Get last date of current year:

In this query we will learn about How to last date of current year in SQL Server.

SELECT dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))) LasteDateOfYear  

36. Select date without time:

In this query we will learn about How to select date without time in SQL Server.

1	`SELECT CONVERT(date, getdate()) AS DateWithoutTime`

37. Select records within two dates:

In this query we will learn about How to select records within two dates in SQL Server.

SELECT * FROM EMPLOYEE  WHERE CONVERT(date, CreatedOn) BETWEEN '1/1/2015' AND '12/31/2016'

Note: Here Employee is my table name and CreatedOn is a name of date column used for filtering.

38. Calculate age from birth date:

In this query we will learn about How to calculate age from birth date in SQL Server.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '05/05/1989'
 
SELECT @tmpdate = @date
 
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
 
SELECT @years AS Years, @months AS Months, @days AS Days

39. Get days between two dates:

In this query we will learn about How to get days between two dates in SQL Server.

1	`SELECT DATEDIFF(DAY,` `'1/1/2016',` `'3/1/2016') DaysDifference`

40. Truncate all tables using single query:

In this query we will learn about How to truncate all database tables using single query in SQL Server.

1	`EXEC sp_MSforeachtable` `'TRUNCATE TABLE ?'`

Note: Please take proper backup before running this query. This query will remove all data from all tables in selected database.

41. Delete all records from all tables having foreign keys:

In this query we will learn about How to delete all rows from all tables in SQL Server which have foreign keys.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

42. Drop all tables:

In this query we will learn about How to drop all tables with single query in SQL Server.

1	`EXEC sys.sp_MSforeachtable @command1 =` `'Drop Table ?'`

43. Reset identity seed after deleting records in SQL Server:

In this query we will learn about How to Reset identity seed after deleting records in SQL Server.

1	`DBCC CHECKIDENT ('[TableName]', RESEED, 0);`

Note:Make sure to delete all records before using this query.

44. Select random rows:

In this query we will learn about How to Select random rows from SQL Server table.

Select N random Rows:

1	`SELECT * FROM TableName ORDER BY NEWID()`

Select N percent of random Rows:

1	`SELECT TOP 5 PERCENT * FROM TableName ORDER BY NEWID()`

45. Swap values of 2 columns:

In this query we will learn about How to swap values of two columns in SQL Server.

Format:

1	`UPDATE TableName SET Column1=Column2, Column2=Column1`

Usage:

1	`UPDATE TableName SET Name=Email,Email=Name`

46. Rename Database:

In this query we will learn about How to rename database in SQL Server.

1	`EXEC sp_renamedb` `'oldName',` `'newName'`

1	`ALTER DATABASE oldName MODIFY NAME = newName`

47. Rename Table:

In this query we will learn about How to rename Table in SQL Server.

1	`EXEC sp_rename` `'OldTableName',` `'NewTableName'`

48. Rename Table Column:

In this query we will learn about How to rename Table Column in SQL Server.

1	`EXEC sp_rename` `'TableName.OldColumnName'` `,` `'NewColumnName',` `'COLUMN'`

49. Check SQL Server version:

In this query we will learn about How to check version of SQL Server.

1	`SELECT @@version`

50. Get list of hard drives with free space:

In this query we will learn about How to get list of system hard drives with available free space using SQL Server.

1	`EXEC master..xp_fixeddrives`

51. Get Id of latest inserted record:

In this query we will learn about How to Id of latest inserted record in SQL Server.

INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','test@gmail.com',30)
     
-- Get Id of latest inserted record
SELECT SCOPE_IDENTITY()

52. Delete duplicate records:

In this query we will learn about How to delete duplicate records in SQL Server.

DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY DuplicateColumn)

53. Display Text of Stored Procedure, Trigger, View :

In this query we will learn about How to Display Text of Stored Procedure, Trigger, View in SQL Server.

exec sp_helptext @objname = 'getInfoFromTable' 
-- Here getInfoFromTable is my storedprocedure name

54. Get List of Primary Key and Foreign Key for a particular table :

In this query we will learn about How to get list of Primary Key and Foreign Key for a particular table in SQL Server.

SELECT  DISTINCT  
Constraint_Name AS [ConstraintName],  
Table_Schema AS [Schema],  
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName' 

55. Get List of Primary Key and Foreign Key of entire database:

In this query we will learn about How to get list of Primary Key and Foreign Key for a entire database in SQL Server.

SELECT  DISTINCT  
Constraint_Name AS [ConstraintName],  
Table_Schema AS [Schema],  
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  

56. Disable Trigger:

In this query we will learn about How to disable trigger in SQL Server.

Disable particular trigger

1	`ALTER TABLE TableName DISABLE TRIGGER TriggerName`

Disable all triggers

1	`ALTER TABLE TableName DISABLE TRIGGER ALL`

57. Enable Trigger:

In this query we will learn about How to enable trigger in SQL Server.

Enable particular trigger

1	`ALTER TABLE TableName ENABBLE TRIGGER TriggerName`

Enable all triggers

1	`ALTER TABLE TableName ENABLE TRIGGER ALL`

58. Make database offline or online :

In this query we will learn about How to make database offline or online in SQL Server.

Make database offline

1	`ALTER DATABASE [ForTesting] SET OFFLINE`

Make Database online

1	`ALTER DATABASE [ForTesting] SET ONLINE`

Note: Make sure to select any other database while using this query.

59. Backup Database :

In this query we will learn about How to take backup of database using script in SQL Server.

1	`BACKUP DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'`

60. Restore Database :

In this query we will learn about How to restore database using script in SQL Server.

1	`RESTORE DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'`

61. Search string in a single table :

In this query we will learn about How to search string within a table in SQL Server.

1	`SELECT * FROM TableName WHERE Name LIKE` `'%TextToSearch%'`

62. Search string in all tables :

In this query we will learn about How to search string in all tables in SQL Server.

DECLARE @SearchStr nvarchar(100)='textToSearch'
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
 
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
 
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + 'WITH (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END
 
SELECT ColumnName, ColumnValue FROM #Results

63. Replace a string in a SQL Server Table :

In this query we will learn about How to replace a string in a SQL Server Table Column.

1	`UPDATE TableName SET ColumnName = REPLACE(ColumnName,` `'OldValue',` `'NewValue')`

64. Left Outer Join :

In this query we will learn about How to use Left outer join in SQL Server.

SELECT * FROM Table1 as t1 
LEFT OUTER JOIN Table2 as t2
on t1.PrimaryColumn=t2.ForeignKeyColumn

65. Right Outer Join :

In this query we will learn about How to use Right outer join in SQL Server.

SELECT * FROM Table1 as t1 
RIGHT OUTER JOIN Table2 as t2
on t1.PrimaryColumn=t2.ForeignKeyColumn

66. Full Outer Join :

In this query we will learn about How to use Full outer join in SQL Server.

SELECT * FROM Table1 as t1 
FULL OUTER JOIN Table2 as t2
on t1.PrimaryColumn=t2.ForeignKeyColumn

67. Inner Join :

In this query we will learn about How to use Inner join in SQL Server.

SELECT * FROM Table1 as t1 
INNER JOIN Table2 as t2
on t1.PrimaryColumn=t2.ForeignKeyColumn

68. Cross Join :

In this query we will learn about How to use Cross join in SQL Server.

SELECT * FROM Table1
CROSS JOIN Table2

69. IF ELSE :

In this query we will learn about How to use IF ELSE Statements in SQL Server.

DECLARE @ValueToCheck INT;
SET @ValueToCheck = 14;
 
IF @ValueToCheck=15
SELECT 'Value is 15' As Result
ELSE IF @ValueToCheck<15
SELECT 'Value is Less than 15' As Result
ELSE IF @ValueToCheck>15
SELECT 'Value is greater than 15' As Result

70. CASE Expression :

In this query we will learn about How to CASE Expression in SQL Server.

SELECT CASE Name
WHEN 'max' THEN 'Name is max'
WHEN 'jack' THEN 'Name is max'
ELSE ' You have selected other user'
END AS Result
FROM TableName

71. Select Second highest salary :

In this query we will learn about How to select second highest salary in SQL Server.

SELECT MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee ); 

72. Paging Example :

In this query we will learn about How to Implement paging in SQL Server.

SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY id) AS RowNum,* FROM Country)as Result
WHERE RowNum>=20 AND RowNum<=40
ORDER BY RowNum

73. Temporary Tables Example :

In this query we will learn about How to use temporary tables in SQL Server.

DECLARE @TempTable TABLE
(       
        Id int primary key,     
        Name varchar(50),
        Email varchar(50)
)
 
INSERT INTO @TempTable VALUES (1,'Max','max@gmail.com'),
                              (2,'Tony','Tony@gmail.com'),
                              (3,'Jack','Jack@Indivar.com')
 
SELECT * FROM @TempTable

74. Delete duplicate rows :

In this query we will learn about How to delete duplicate rows in SQL Server.

DELETE FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY DuplicateColumn1, DuplicateColumn2)

75. Reset Identity Seed :

In this query we will learn about How to Reset identity seed after deleting records in SQL Server.

1	`DBCC CHECKIDENT ('[YourTableName]', RESEED, 0);`

Note: Here 0 is the number-1 from where you want to start your auto increment. Suppose you wants to start auto increment your primary key value from 50 then you need to pass 49 in the above method.

Buscar contenidos

viernes, 16 de agosto de 2024

Ejemplo SP + JOB para traslado de tabla/Históricos

miércoles, 14 de agosto de 2024

Ejemplo Azure Service Bus, N:1, N:N

miércoles, 12 de julio de 2023

Reiniciar (forzado) VM en Azure

martes, 31 de enero de 2023

Función en Javascript para mascara de moneda

miércoles, 14 de septiembre de 2022

75 Important queries in SQL Server every developer should know

75 Important queries in SQL Server every developer should know

1. Create Table:

2. Create table with primary key:

3. Create table with primary key and Identity Column:

4. Insert values into SQL Server table:

5. Insert multiple rows in a single query:

6. Update query:

Update single record:

Update all records:

7. Delete query:

Delete single record:

Delete multiple records:

8. Select:

Select all columns from a tables:

Select specific columns:

9. Create View:

Usage:

10. Create Stored procedure:

11. Get all column names from table:

Get column names with data type:

12. Search column name from database:

13. Get all user created tables:

14. Get all view names:

15. Get all stored procedure names:

16. Counting rows for all tables at once:

17. Get Comma Separated List of all columns in table:

18. Generate ADO .net parameters:

19. Retrieve List of All Database names:

20. Check default Language of SQL Server:

21. Check server name:

22. Add columns to existing table:

Syntax

Usage

23. Remove column from a table:

24. Check column having NULL values only from a table:

25. Get list of tables without primary key:

26. Get list of tables without having identity column:

27. Get month names with month numbers:

28. Get name of current month:

29. Get name of day:

30. Get first date of current month:

31. Get last date of current month:

32. Get first date of previous month:

33. Get last date of previous month:

34. Get first date of current year:

35. Get last date of current year:

36. Select date without time:

37. Select records within two dates:

38. Calculate age from birth date:

39. Get days between two dates:

40. Truncate all tables using single query:

41. Delete all records from all tables having foreign keys:

42. Drop all tables:

43. Reset identity seed after deleting records in SQL Server:

44. Select random rows:

Select N random Rows:

Select N percent of random Rows:

45. Swap values of 2 columns:

Format:

Usage:

46. Rename Database:

47. Rename Table:

48. Rename Table Column:

49. Check SQL Server version:

50. Get list of hard drives with free space:

51. Get Id of latest inserted record:

52. Delete duplicate records:

53. Display Text of Stored Procedure, Trigger, View :

54. Get List of Primary Key and Foreign Key for a particular table :