11/28/18

Inside the fake science factory...

I just saw an interesting DEF CON session. https://www.youtube.com/watch?v=ras_VYgA77Q

Inside the fake science factory, it's about fake news ugly sibling. There are organisations that offer a "platform" for "scientists" to publish their "scientific papers" and hosting "scientific conferences" to gain credibility to the "reports" and "scientists".

See it, it might help you to discover if what you are looking at is published through at least a few of these fake science factories.

Try it, enter below search in your google search. Replace "your search object" with what you want to check up inside the quotes:


"your search object" site:omicsonline.org OR site:sciencedomain.org OR
site:omicsgrouponline.org OR site:waset.us OR
site:waset.org OR site:waet.org OR
site:academicjournals.org OR site:imed.pub OR
site:thescipub.com OR site:austinpublishinggroup.com OR
site:iosrjournals.org OR site:alliedacademies.com OR
site:conferenceseries.com OR
site:sciencepublishinggroup.com OR site:davidpublisher.org


N.B. just because you find "scientific stuff or people" with the search doesn't mean it is fake. It only means they probably made the effort publishing their "science stuff" with a "fake science factory" and that they paid money for it.

11/9/18

Skriptet

USE minaAdresser;
GO
CREATE SCHEMA Tabeller;
GO
CREATE SCHEMA Adresser;
GO
CREATE TABLE Tabeller.Namn
 (
  NamnID int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Tabeller_Namn
   PRIMARY KEY CLUSTERED,
  Namn nvarchar(255) NOT NULL
   CONSTRAINT uq_Tabeller_Namn_Namn
   UNIQUE
 )
CREATE TABLE Tabeller.Adresser_Adress
 (
  AdressID  int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Adresser_Adress
   PRIMARY KEY CLUSTERED,
  Förnamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Förnamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Efternamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Efternamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gata int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Gata
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gatunummer nchar(15) NOT NULL
   CONSTRAINT df_Adresser_Adress_Gatunummer
   DEFAULT (''),
  Postnummer char(6) NOT NULL
   CONSTRAINT ck_Adresser_Adress_Gatunummer
   CHECK (Postnummer LIKE '[1-9][0-9][0-9] [0-9][0-9]'),
  Ort int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Ort
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID)
 )
GO
CREATE VIEW Adresser.Adress
AS
SELECT
 a.AdressID,
 fn.Namn AS Förnamn,
 en.Namn AS Efternamn,
 g.Namn AS Gata,
 a.Gatunummer,
 a.Postnummer,
 o.Namn AS Ort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE VIEW Adresser.Utskrift
AS
SELECT
 a.AdressID,
 fn.Namn + ' ' + en.Namn    AS Mottagare,
 g.Namn + ' ' + a.Gatunummer   AS Postadress,
 a.Postnummer + ' ' + UPPER(o.Namn) AS Postort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE FUNCTION Adresser.hämtaNamn(@NamnID int)
RETURNS nvarchar(255)
AS
BEGIN
RETURN(SELECT Namn FROM Tabeller.Namn WHERE NamnID = @NamnID)
END
GO
CREATE FUNCTION Adresser.hämtaNamnID(@Namn nvarchar(255))
RETURNS int
AS
BEGIN
RETURN(SELECT NamnID FROM Tabeller.Namn WHERE Namn = @Namn)
END
GO
CREATE PROCEDURE Adresser.skapaNamn @Namn nvarchar(255), @NamnID int OUTPUT
AS
BEGIN
DECLARE @Retur int = 0
IF @Namn IS NULL
BEGIN
 SET @Namn = ''
 SET @Retur += 10
END
SET @NamnID = Adresser.hämtaNamnID(@Namn)
IF @NamnID IS NULL
BEGIN
 INSERT INTO Tabeller.Namn(Namn) VALUES(@Namn)
 SET @NamnID = SCOPE_IDENTITY()
 SET @Retur += 1
END
RETURN @Retur
END
GO
CREATE PROCEDURE Adresser.uppdateraAdress
 @Förnamn nvarchar(255),
 @Efternamn nvarchar(255),
 @Gata nvarchar(255),
 @Gatunummer nvarchar(255),
 @Postnummer nvarchar(255),
 @Ort nvarchar(255),
 @AdressID int OUTPUT,
 @updateAdressID int = -1
AS
BEGIN
DECLARE
 @Retur int = 0,
 @Exec int = 0,
 @FörnamnID int,
 @EfternamnID int,
 @GataID int,
 @OrtID int
IF @Postnummer NOT LIKE '[1-9][0-9][0-9] [0-9][0-9]' SET @Retur = -3
ELSE IF @Gatunummer IS NULL SET @Retur = -2
ELSE
BEGIN
EXEC @Exec = Adresser.skapaNamn @Förnamn, @FörnamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Efternamn, @EfternamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 1000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Gata, @GataID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 100000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Ort, @OrtID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10000000
 SET @Exec = 0
END
END
IF @Retur >= 0
BEGIN
IF @updateAdressID = -1
BEGIN
SELECT @AdressID = AdressID FROM Tabeller.Adresser_Adress
WHERE
 Förnamn = @FörnamnID
AND Efternamn = @EfternamnID
AND Gata = @GataID
AND Gatunummer = @Gatunummer
AND Postnummer = @Postnummer
AND Ort = @OrtID
IF @AdressID IS NULL
BEGIN
INSERT INTO Tabeller.Adresser_Adress(Förnamn, Efternamn, Gata, Gatunummer, Postnummer, Ort)
VALUES(@FörnamnID, @EfternamnID, @GataID, @Gatunummer, @Postnummer, @OrtID)
SET @AdressID = SCOPE_IDENTITY()
SET @Retur += 1
END
END
ELSE
BEGIN
DECLARE @updated TABLE (AdressID int)
UPDATE Tabeller.Adresser_Adress
SET
 Förnamn = @FörnamnID,
 Efternamn = @EfternamnID,
 Gata = @GataID,
 Gatunummer = @Gatunummer,
 Postnummer = @Postnummer,
 Ort = @OrtID
OUTPUT inserted.AdressID INTO @updated
WHERE AdressID = @updateAdressID
SELECT @AdressID = AdressID FROM @updated WHERE AdressID = @updateAdressID
SET @Retur += 2
END
END
RETURN @Retur
END
GO
USE master

11/6/18

Are you missing the Database Diagrams from the new SQL Server Management Studio?

If you just installed the latest preview of SSMS 18.0 you might have realized that the Database Diagrams are gone. The feature is deprecated.

Download the preview here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Scroll down to the SSMS 18.0 (Preview) and try it out, there is also improvements.

So, what to do? Remember there is a bunch of INFORMATION_SCHEMA-views you can use.
This is an example how you can do it, the example is not perfect but help you out a bit.

As Always, I do not take any responsibility for any code you might find on my blog. I write queries for fun and business. Small snippets might end up here.

9/25/18

SQL Server vNext

Just want to tell you all that next awesomeness of SQL Server is available to test out. Check this out, https://www.microsoft.com/en-us/sql-server/sql-server-2019.

Trust me, you will love it!

11/15/17

Some In Memory OLTP analytical scripts

I will add descriptions about this in time.

Please use this as an inspiration for your own In Memory OLTP analysis.
CREATE DATABASE InMemDemos
ON PRIMARY (NAME=InMemDemos_Data, FILENAME='D:\Data\InMemDemos_Data.mdf', SIZE=10MB, FILEGROWTH=10MB),
FILEGROUP InMemDemos_FG CONTAINS MEMORY_OPTIMIZED_DATA (NAME=InMemDemos_FG, FILENAME='D:\Data\InMemDemos_FG')
LOG ON (NAME=InMemDemos_Log, FILENAME='D:\Data\InMemDemos_Log.ldf', SIZE=10MB, FILEGROWTH=10MB)
First part creates the database, Second part creates three tables:
USE InMemDemos
GO
CREATE TABLE InMem_BC100
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
Let us populate the tables :
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC100(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
How about some checkpoint statistics:
SELECT * FROM sys.dm_db_xtp_checkpoint_stats
 And status about the Data and Delta files:
SELECT
da.container_guid,
da.relative_file_path AS Data_File,
de.relative_file_path AS Delta_File,
da.internal_storage_slot,
da.state_desc,
da.file_size_in_bytes AS Data_File_Size,
da.file_size_used_in_bytes AS Data_File_Size_Used,
de.file_size_in_bytes AS Delta_File_Size,
de.file_size_used_in_bytes AS Delta_File_Size_Used,
da.inserted_row_count,
de.deleted_row_count
FROM sys.dm_db_xtp_checkpoint_files da
INNER JOIN sys.dm_db_xtp_checkpoint_files de
ON da.checkpoint_file_id = de.checkpoint_pair_file_id
WHERE da.file_type_desc = 'DATA' AND de.file_type_desc = 'DELTA'
And continue with some Table statistics:
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
xtms.memory_allocated_for_table_kb,
xtms.memory_used_by_table_kb,
xtms.memory_allocated_for_indexes_kb,
xtms.memory_used_by_indexes_kb
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.dm_db_xtp_table_memory_stats xtms
ON t.object_id = xtms.object_id
 All the way to specific indexes:
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
i.name AS Index_Name,
i.type_desc AS Index_Type,
xhis.total_bucket_count,
xhis.empty_bucket_count,
xhis.avg_chain_length,
xhis.max_chain_length,
xmc.allocated_bytes,
xmc.used_bytes,
xis.*
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
INNER JOIN sys.dm_db_xtp_index_stats xis
ON t.object_id = xis.object_id AND i.index_id = xis.index_id
INNER JOIN sys.dm_db_xtp_hash_index_stats xhis
ON xis.object_id = xhis.object_id AND xis.index_id = xhis.index_id
INNER JOIN sys.dm_db_xtp_memory_consumers xmc
ON xis.object_id = xmc.object_id AND xis.index_id = xmc.index_id
Remeber to throw some checkpoint from time to time:
CHECKPOINT
 Ok, I'm done now and I hope you can make use of the queries I shared here.