- 21
- 52 912
Alex D
Latvia
Приєднався 23 кві 2012
On this channel you will find many useful tricks for programming on MS T-SQL
T-SQL: CONDITIONS WITH MULTIPLE WILDCARDS IN MULTIPLE TABLE COLUMNS
SQL script:
www.dargelis.net/idea/sql-scripts/multi_wildcards.sql
www.dargelis.net/idea/sql-scripts/multi_wildcards.sql
Переглядів: 2 410
Відео
How to get sample databases for MS SQL Server?
Переглядів 1625 років тому
Northwind www.dargelis.net/idea/sql-scripts/instnwnd.sql Pubs www.dargelis.net/idea/sql-scripts/instpubs.sql
T-SQL: Hiding SQL code in view, SP or functions (WITH ENCRYPTION)
Переглядів 2,2 тис.5 років тому
WITH ENCRYPTION
T-SQL: column-level encryption
Переглядів 20 тис.6 років тому
step 1 USE TESTDB ; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123Alex!@#$789'; DROP MASTER KEY step 2 CREATE TABLE TABLE3 (ID int, PERSONNAME nvarchar(200), AGE int, HOBBY nvarchar(200), SALARY varbinary(128)) step 3 CREATE CERTIFICATE MyCertificate1 WITH SUBJECT = 'Secret info - Salary'; GO drop CERTIFICATE MyCertificate1 CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_256 ENCRYPTION BY...
T-SQL: data reading from SQL database using excel custom function. PART 2
Переглядів 3,1 тис.6 років тому
CREATE TABLE TABLE2 (ORD nvarchar(10), DT datetime, CUSTOMER nvarchar(200), PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO TABLE2 VALUES ('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55), ('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00), ('003','2018-07-02','Customer Apple','CHAIR X1',30,15), ('004','2018-07-03','Customer Orange','C...
T-SQL: data reading from SQL database using excel custom function PART 1
Переглядів 1,4 тис.6 років тому
CREATE TABLE TABLE1 (ID int, PERSONNAME nvarchar(200), AGE int, HOBBY nvarchar(200)) INSERT INTO TABLE1 VALUES (1,'Alex',40,'skateboarding'), (2,'John',32,'wrestling'), (3,'Asti',25,'jogging'), (4,'Jay',53,'football'), (5,'Selena',40,'yoga') INSERT INTO TABLE1 VALUES (6,'Denis',95,'yoga') CREATE TABLE TABLE2 (DT datetime, PERSON_ID int, PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE ...
T-SQL: How to calculate the total number of working days between two dates?
Переглядів 3,7 тис.6 років тому
FULL TSQL SCRIPT IS HERE: www.dargelis.net/idea/sql-scripts/WORKING_DAYS.sql
T-SQL: PIVOT
Переглядів 5 тис.6 років тому
CREATE TABLE TABLE2 (ORD nvarchar(10), DT datetime, CUSTOMER nvarchar(200), PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO TABLE2 VALUES ('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55), ('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00), ('003','2018-07-02','Customer Apple','CHAIR X1',30,15), ('004','2018-07-03','Customer Orange','C...
T-SQL: List of integer numbers and dates in one query, without cursor and stored procedure
Переглядів 3726 років тому
WITH DTcounter(NUMB, DT) as ( SELECT ROW_NUMBER() OVER (ORDER BY s1.object_id), dateadd(dd,ROW_NUMBER() OVER (ORDER BY s1.object_id),CURRENT_TIMESTAMP) FROM sys.all_columns as s1 counter about 9500 CROSS JOIN sys.all_columns as s2 multiply on another 9500 ) SELECT NUMB, DT FROM DTcounter WHERE NUMB between 1 and 1000 CREATE TABLE TABLE2 (ORD nvarchar(10), DT datetime, CUSTOMER nvarchar(200), PR...
T-SQL: Command OVER. Alternative for subquery. Running totals.Partitioning and ordering.
Переглядів 6856 років тому
CREATE TABLE TABLE2 (ORD nvarchar(10), DT datetime, CUSTOMER nvarchar(200), PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO TABLE2 VALUES ('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55), ('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00), ('003','2018-07-02','Customer Apple','CHAIR X1',30,15), ('004','2018-07-03','Customer Orange','C...
T-SQL: Files import and export using T-SQL for SQL Server
Переглядів 3 тис.6 років тому
DECLARE @FOLDER as nvarchar(200) DECLARE @FILE as nvarchar(200) DECLARE @SQL as nvarchar(200) CREATE TABLE MyPictures ( ID int, Folder nvarchar (200), FName nvarchar (200), Img varbinary (max) ) INSERT INTO MyPictures SELECT 1,'C:\!all\IDEA_A\2 images\','arrow.png', BulkColumn FROM OPENROWSET (BULK 'C:\!all\IDEA_A\2 images\arrow.png', SINGLE_BLOB) as image SET @FOLDER = 'C:\!all\IDEA_A\2 images...
T-SQL: How to get previous and next record from a selection?
Переглядів 1,1 тис.6 років тому
select top 100 SC01001, LAG(SC01001) OVER (ORDER BY SC01001) as PreviousRecord, LEAD(SC01001) OVER (ORDER BY SC01001) as NextRecord from SC010100
T-SQL: command WITH for temporary results
Переглядів 3456 років тому
CREATE TABLE TABLE1 (ID int, PERSONNAME nvarchar(200), AGE int, HOBBY nvarchar(200)) INSERT INTO TABLE1 VALUES (1,'Alex',40,'skateboarding'), (2,'John',32,'wrestling'), (3,'Asti',25,'jogging'), (4,'Jay',53,'football'), (5,'Selena',40,'yoga') CREATE TABLE TABLE2 (DT datetime, PERSON_ID int, PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO TABLE2 VALUES ('2018-...
T-SQL: Column values conversion into one string
Переглядів 8586 років тому
DECLARE @TABLE2 TABLE (DT datetime, PERSON_ID int, PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO @TABLE2 VALUES ('2018-07-01',1,'TABLE 40x60',50,32.55), ('2018-07-02',4,'TABLE 100x60',10,65.00), ('2018-07-02',2,'CHAIR X1',30,15), ('2018-07-03',4,'CHAIR X2',20,13), ('2018-07-03',1,'PC IBM',10,800), ('2018-07-04',5,'PC HP',10,720.12), ('2018-07-04',4,'PC DEL...
T-SQL: Query result conversion to XML string
Переглядів 9566 років тому
DECLARE @TABLE2 TABLE (DT datetime, PERSON_ID int, PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO @TABLE2 VALUES ('2018-07-01',1,'TABLE 40x60',50,32.55), ('2018-07-02',4,'TABLE 100x60',10,65.00), ('2018-07-02',2,'CHAIR X1',30,15), ('2018-07-03',4,'CHAIR X2',20,13), ('2018-07-03',1,'PC IBM',10,800), ('2018-07-04',5,'PC HP',10,720.12), ('2018-07-04',4,'PC DEL...
T-SQL: comparison of tables with similar structure EXCEPT & INTERSECT
Переглядів 7966 років тому
T-SQL: comparison of tables with similar structure EXCEPT & INTERSECT
T-SQL: Extracting values from XML with multiple nodes
Переглядів 2,7 тис.6 років тому
T-SQL: Extracting values from XML with multiple nodes
T-SQL: Split a string in to multiple substrings
Переглядів 1,2 тис.6 років тому
T-SQL: Split a string in to multiple substrings
Getting Started with SQL Server. Get the sample database.
Переглядів 526 років тому
Getting Started with SQL Server. Get the sample database.
thanks for your video 😀
It doesn't work for me
Hi..how to select all values products in one x path or do we have any other way to get pls help.. like value.('//product[1,2,3] )
Nice video and good explanation. I assume this only encrypts the data at rest correct? Not in transit?
Great explanation. Is it still relevant with recent version of SQL Server?
DOU YOU HAVE THE SAME FOR MYSQL?
Best Video with all the steps/sample code provided. Thanks a lot Alex!
Hi Alex, If we need to remove encryption on Salary column. Then what will have to do ? Please suggest . Thanks
awesome! thanks a lot
Great! Thanks so much.
Thank you Alex, you are a GEM
great vidoe , what if i backup and restore the db to another server?> thanks
Nice, very nice. Thank you.
Great video. Simple and clear. Alex, thank you very much!
Interesting trick. Thank you.
Hi. It's very interesting and from my point of view it's useful knowledge how to make encryptions. Especially if we are talking about sensitive data. Thank you for this video.
Thanks for sharing this but, I've a problem when I run Step 5. I'm inserting 5 digits into the Salary column and only the first digit is displayed when I run Step 5. any idea why is that happening? Thanks!
Try to use varchar(10) instead of varchar
Maybe you aren't doing the righ convertion, remember that you need to put a lenght
Fantastic, this is only for advanced user.
Really nice video :)
it's awesome!!! It's really helpful. Please continue to make such excellent videos. I have question, Can encrypt whole SQL table?
Hi. Your video lessons are very interesting and helpful. I have question, how possible to check relations between tables (how tables are connecti with each other) in SQL via script or standard SQL server capability?