Relationship design for areas, managers and general manager Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also an employee as the boss of all areas. My question is about how I should indicate which employee is the boss of all areas.
    | employee_id |    name    |

    |      1      |    Mark    |

    |      2      |    Carol   |

    |      3      |    Adam    |

    |      4      |    Andy    |

    | area_id |    area_name    | manager |

    |    1    |    Chemistry    |    2    |

    |    2    |    Electronics  |    4    |

    |    3    |    Medicine     |    3    |

    |    4    |      ALL        |    1    |

At the moment what has occurred to me is to create an occurrence called "ALL" within the relation "AREA" to indicate the head of all areas, however I am not sure if it is the best way or the correct way to do it . Any other idea?

It's ok to have an "ALL" value as an Area but it might be preferable and more flexible to add a third table, as a linking table, called EmployeeArea instead.
The EmployeeArea table is where you can store the area_id and employee_id (as manager_id), to represent the technically many-to-many relationship between the tables. Then the boss of all areas would have a record in there for each Area.
This is more normalized of a design, and is more flexible if for some reason there needs to be multiple bosses of all areas, or special cases where multiple employees are responsible for the same area.


Most efficient way of finding rows in a table that don't exist in another -- SQL Server What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the unique key is a VARCHAR(60) field? (I know, a bad idea to have this column as unique key, but I can't change it).

Specifically:

Would using a CHAR(60) versus VARCHAR(60) going to help?
How about hashing the key to binary(20)? Do you expect a significant
improvement in join?

SELECT tbl_A.id
FROM tbl_A
LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id
WHERE tbl_B.id IS NULL

This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations.

As for varchar and char: char is more efficient when using data that is all the same length.

Here is an SQLFiddle example http://sqlfiddle.com/#!9/bbbbc3/1


Normalize or Denormalize - Which would be better I have the following data structures:

Regions ( About 150 Regions )
-------
id    Region_Name

Sub Regions (10 -12 for each region)
------------------------------------
id  Sub_Region_Name  Region_id

Showrooms ( 150 - 170 per Sub Region )
--------------------------------------
id  Showroom_Name  Sub_Region_id

Users ( 750 - 1,000 per Showroom)
-----------------------------------------
id  UserName Address  Age  Showroom_Id

What would be the best option for the Users table?

Should I have all the users in one table, which would take the number of rows to 20 million; or should I use a table structure like Users_Arizona, Users_Ohio etc. as I am most interested in users grouped by region.

The database is not open to public interaction.

First of all the data model must be accurate. It must capture the values required by the business in order to perform its functions and answer its queries.

In this particular case I believe you want to associate each user with one particular showroom. If my belief is correct then the model you show fulfils this requirement.

The second concern is to make the system fast. You seem worried that having between 160 million and 300 million rows in a single Users table will be unworkable. You propose to preemptively address this by having multiple Users tables, one per region, for about 150 Users_xxx tables in total. The rationale for splitting at this level is that the majority of the queries will aggregate by region.

I would suggest retaining a single user table, for DB maintenance and application development reasons.

It is unlikely the list of regions is permanently fixed. This means new regions will be introduced and current ones removed. In turn that means tables must be created and dropped, with application changes to match. What should be a simple data change has become a system maintenance project.

A typical application function is to find a user based on natural keys, surname for example. With a single table this is implemented as

select <columns>
from Users
where surname = 'the-name'

Surname (and other columns used in queries) can be indexed to make this fast. Covering indexes can be defined to keep all the required data contiguous on disk. Range scans can be made fast with disk read-ahead etc.

With split tables it is

select <columns>
from Users_AAA
where surname = 'the-name'

union all

select <columns>
from Users_BBB
where surname = 'the-name'

union all

...
repeat 147 more times
...

select <columns>
from Users_ZZZ
where surname = 'the-name'

Each table will have its own little bit of disk. These are unlikely to be contiguous. Every query will have to hit every table, even if they're indexed. It is unlikely to be fast. Once again this query must change every time the list of regions changes. It can be hidden from the application somewhat by enclosing it in a view, but the maintenance task remains.

As a starting point, I would maintain a single Users table with a foreign key to showroom, just as you've shown. If testing proved to be a problem in practice at the anticipated scale, on production-sized hardware, I'd consider propagating the key of Region into SubRegion and Showroom, giving them compound keys. Users table can then be indexed / clustered by Region to satisfy the given query. I may even use the natural key for this, rather than the surrogate, to remove further steps from the execution plan. The foreign key constraints will ensure only correct Region_Id appear in the child tables.

This could also be achieved by demnormalization. Region_Id column is added to Users but not to SubRegion or Showroom. The value is copied in when a row is written to Users. Assuming a user never switches Showroom/ Subregion / Region you're done. The system cannot help you ensure Users.Showroom_Id and Users.Region_Id correspond to each other.


Compression in MongoDb 3.2 does not work I installed MongoDb 3.2, and configured snappy compression in mongodb configuration file:

engine: wiredTiger
   wiredTiger:
       engineConfig:
           cacheSizeGB: 99
           journalCompressor: none
       collectionConfig:
           blockCompressor: snappy
       indexConfig:
           prefixCompression: true

However I do not think compression actually work as I could see average object size in the collection is about the same (I filled collection created from scratch by the same objects right after configuration change and service restart):

MMAPv1 no-compression avgObjSize: 5kb
WiredTiger snappy avgObjSize: 4kb
WiredTiger zlib avgObjSize: 4kb

when I run stats on collection here is what I see for "snappy":

"compression" : {
        "compressed pages read" : NumberInt(0),
        "compressed pages written" : NumberInt(5),
        "page written failed to compress" : NumberInt(0),
        "page written was too small to compress" : NumberInt(4),
        "raw compression call failed, additional data available" : NumberInt(0),
        "raw compression call failed, no additional data available" : NumberInt(0),
        "raw compression call succeeded" : NumberInt(0)
    },

for zlib:

{
    "compressed pages read" : NumberInt(0),
    "compressed pages written" : NumberInt(1),
    "page written failed to compress" : NumberInt(0),
    "page written was too small to compress" : NumberInt(3),
    "raw compression call failed, additional data available" : NumberInt(0),
    "raw compression call failed, no additional data available" : NumberInt(1),
    "raw compression call succeeded" : NumberInt(2)
}

Not sure how to read this info, and not sure how can I detect what's wrong with compression.

I should also say I had only few (like 10) objects in each scenario. Would it be enough to accurately measure average object compression ratio?

UPDATE: with 1188 documents mongodb ver3 shown around 50% better compression ratio comparing to mongodb ver2.6 (MMAPv1), however got no difference between snappy and zlib: dataSize and avgObjSize are the same for some reason. When I request statistics it shown WiredTiger:creationString:...block_compressor=zlib.. for the first database and block_compressor=snappy for the second one, so databases and collections seemed to be created with right compression algorithms

--version 3.4.10 Enterprise

compression will not change the avgObjSize. It will just improve the compression ratio saving disk space; I tested it for a collection (documents: 11553423 and avgObjSize= 6182) worth 21GB  with snappy, when same created with zlib, size of collection reduced to 12GB (avgObjSize= 6182).


How to migrate a database from a local to a remote server? I have a postgres db that I have created and need to deploy remotely. From reading online I have understood that to do that I need to modify the following files :

pg_hba.conf
postgresql.conf

From what I read I need to modify them and somehow add them to my database?

My question is:

How do I modify the 2 files to deploy a database online? I read the documentation but its too complicated for my understanding.(ie what is needed and why?)
How do I add them to my database online? My idea was to create a database dump of the local DB, then push the dump on a new database on the remote computer, but how do these 2 files fit in there? Where should there be placed? I cannot find details on this.

note. I have never done distributed programming before, my question might be naive but I really have no idea how to proceed.

I am using osX operating system,but will be using linux on the remote computer.

The two configuration files apply to the server, so both your local and remote server already have those files and their contents is specific to the installation.

Assuming your remote server is already up and running, all you need to do is dump the database you want to deploy to a file and then load the dump file in your remote server. You do not have to do anything with the configuration files.

You can use pg_dump to dump the local database to file, doing something like this:

pg_dump -U postgres -Fc my_db > my_db.db

But check all the options of the pg_dump program.

Loading the database on your remote server is just as easy with pg_restore:

pg_restore -h <ip remote server> -p <port remote server> -U postgres -C my_db.db

On the remote server you need to specify a user who can create databases, if you do not have access to the postgres role.

If you are on some flavour of *nix you can even pipe pg_dump output into pg_restore input and do it all in one go:

pg_dump -U postgres -Fc my_db | pg_restore -h <ip> -p <port> -U postgres -C

Note that pg_dump will ask for a password on the local server (if at all) and pg_restore will for a password on the remote server (if at all).

Both programs have a lot of options. Be sure to read the documentation fully and use options to match your situation before coming back to tell me that it doesn't work.


mysql: find column data size on disk I have fairly large MyISAM table with longtext field in it

Table size is somewhat 16GB.

I need to know if clearing this column in old records helps to keep table small

I can retrieve table wise Data_length using information_schema.tables

Is there a way to find Data_length just for this one column?

Here is how you find the sum of the lengths the LONGTEXT column of all rows

SELECT SUM(field_length) TotalFieldLength FROM
(SELECT LENGTH(LongTextColumnName) field_length FROM mydb.mytable) A;

Once you delete the rows you want, just compress the file

You can do it in a single command

OPTIMIZE TABLE mydb.mytable;

or in stages

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew DISABLE KEYS;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytablenew ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;
ANALYZE TABLE mydb.mytable;

Give it a Try !!!


create database table with a owner in my SQL Can someone tell me what is the SQL command to create a table with specific owner?

There is no concept such as OWNER in mySQL for databases or for tables.

You will need to enforce security in the traditional way with granting very specific user privileges.

for e.g. for inserts: GRANT INSERT ON database.* TO 'username'@'hostname' ;


How to do PostgreSQL replication on CentOS 7? Followed this guide to do the PostgreSQL replication:

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps

I didn't do this:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"

After I configed both master and slave nodes, test to create a table on master, didn't find new data been synced to slave.

From this guide I saw need to add a archive_command config in the recovery.conf file:

https://www.server-world.info/en/note?os=CentOS_7&p=postgresql&f=3

archive_command = 'cp %p /var/lib/pgsql/archive/%f'

But PostgreSQL 9.6 maybe not support that feature. So how can it been synced automatically?

Addition

It's helpful:

https://dalibo.github.io/PAF/Quick_Start-CentOS-7.html

The archive_command setting doesn't fit the recovery.conf but the postgresql.conf file.

The documentation you're following is deprecated.

Please switch to the official documentation.

Here are the steps you need to perform:

Primary node settings (postgresql.conf, pg_hba.conf)
Primary node backup (pg_basebackup, pgbarman, pgbackrest... Whatever)
Restoring on secondary node (manually, pgbarman, pgbackrest... Whatever)
Secondary node settings (recovery.conf, postgresql.conf, eventually pg_hba.conf)

The part you didn't do is simply the initial backup/restore. You can't do replication without it.


Add values from two existing column into new columns for a total We have two existing columns in our database named 'ClientIncome' and 'PartnerIncome'. We would like to add a new column named 'HouseholdIncome'

We would like to add the values from 'ClientIncome' and 'PartnerIncome' into the new 'HouseholdIncome' column?

Here is a small example of our database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] [float] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Incomes] ON

GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (1, 130.77, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (2, 294.82, 339.28, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (3, 0, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (4, 0, 396.82, NULL)
GO
SET IDENTITY_INSERT [dbo].[Incomes] OFF
GO

The table we would like to update has around 5 million rows, so any help with adding this new column would be appreciated.

Just change your table create statement to use a computed column.

CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.)
)

If your table is already created, you can add a computed column:

ALTER TABLE dbo.Incomes ADD HouseholdIncome
    AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.);


Select columns by file containing primary keys I have a text file with primary keys. Each line contains one primary key. Now I want to select the rows based on the input file. I can not find the right option for psql to do this. What is the canonical way for PostgreSQL to do this job?
I know I can do
psql -c 'select * from table' db | grep -f primary_keys

But this dumps the whole database. How can I avoid it?

This assumes you are on a shell and have the tr command in the PATH:
psql -c "SELECT * FROM mytable WHERE id IN (`tr '\n' , <file`NULL)"

Every newline character is replaced with ,, and the NULL in the end takes care of the final comma.


SQL Agent still attempting replication for deleted publication We recently retired a replication publication, deleting each subscription (push subscriptions) and then deleting the publication, all from within SSMS.  However, we continue to get the following message (once every minute) in the four days since deleting this publication.

  Replication-Replication Distribution Subsystem: agent (null) failed. The publication 'Replicate_ASG_Reference_Data' does not exist.

Why is it still trying to run a deleted publication, and how do I stop it?

Additional information: This is on SQL Server 2008 SP3 running on Windows Server 2008 R2.

It looks like you have some orphaned replication bits and/or orphaned replication agent jobs.

Note that Distribution.dbo.sp_MSremove_published_jobs will not remove your Distribution Agent jobs.  You will have to manually delete the orphaned job(s).

Locate the job(s) under SQL Server Agent -> Jobs.  The Distribution Agent job names will have the format Publisher-PublicationDatabase-Publication-Subscriber-integer.  Right-click the job -> Delete.

I hope this helps.


How to get the consolidated ServiceID with the sum of File Size associated with over an year Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.

Complete Code (For reference)

If(OBJECT_ID('tempdb..#temp1') Is Not Null)
Begin
    Drop Table #Temp1
End

GO

If(OBJECT_ID('tempdb..#temp2') Is Not Null)
Begin
    Drop Table #Temp2
End
GO

If(OBJECT_ID('tempdb..#temp3') Is Not Null)
Begin
    Drop Table #Temp3
End

GO

If(OBJECT_ID('tempdb..#temp4') Is Not Null)
Begin
    Drop Table #Temp4
End
GO

SELECT *
INTO #Temp1
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1980' from table1
    ) a

--1981
SELECT *
INTO #Temp2
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1981'
        from table2
    ) b

--1982
SELECT *
INTO #Temp3
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1982' from table3
) c

--1983

SELECT *
INTO #Temp4
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1983' from table4
    ) d

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Below are the 4 queries existing in #temp tables I finally worked and their outputs:

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Output of the above 4 queries:

My Question : How can I get the below output using the above 4 queries ? Please help me , the way I need to join . I tried my way to join them but sure it is wrong so reached out for help.

Thank you

Here is an example of how this could be done.

Declare @Query1 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query2 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query3 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query4 table (id int, ServiceID int, Year int, FileSize1 int)

insert into @Query1(id,ServiceID,Year,FileSize1) values
(1,121,1980,10),
(2,122,1980,20),
(3,123,1980,30),
(4,121,1980,40)

insert into @Query2(id,ServiceID,Year,FileSize1) values
(1,121,1981,10),
(2,122,1981,20),
(3,123,1981,30),
(4,123,1981,40)

insert into @Query3(id,ServiceID,Year,FileSize1) values
(1,121,1982,10),
(2,122,1982,20),
(3,124,1982,30),
(4,125,1982,40)

insert into @Query4(id,ServiceID,Year,FileSize1) values
(1,123,1983,10),
(2,121,1983,20),
(3,122,1983,30),
(4,121,1983,40)

;With _cte as
(
select ServiceID,Year,FileSize1 as FileSize1 from @Query1
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query2
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query3
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query4
)

select ServiceID,
sum(case when year = 1980 then FileSize1 end) as FileSize_1980,
sum(case when year = 1981 then FileSize1 end) as FileSize_1981,
sum(case when year = 1982 then FileSize1 end) as FileSize_1982,
sum(case when year = 1983 then FileSize1 end) as FileSize_1983
 from _cte
group by ServiceID

| ServiceID | FileSize_1980 | FileSize_1981 | FileSize_1982 | FileSize_1983 |
|-----------|---------------|---------------|---------------|---------------|
| 121       | 50            | 10            | 10            | 60            |
| 122       | 20            | 20            | 20            | 30            |
| 123       | 30            | 70            | NULL          | 10            |
| 124       | NULL          | NULL          | 30            | NULL          |
| 125       | NULL          | NULL          | 40            | NULL          |


SQL Server - Separating data, log, and TempDB files on a SAN I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) from the SAN administrator, but given the new vendor's recommendation, is there any point in creating separate LUNs? Or would I see the same performance if everything was in one LUN since it would span all disks anyway?

Great article here, but doesn't quite address my exact situation:
http://www.brentozar.com/archive/2008/08/sql-server-on-a-san-dedicated-or-shared-drives/

One thing to consider is that the log files are sequential writes where as the data files are non sequential.  That is one of the reasons for separate LUNs.  Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential.  If you add in a data file then the spindles have to skip around and you lose some performance.  I'm hoping I got the right terminology there as I'm not all that familiar with SANs themselves.  The idea behind it should be however.

Frequently vendor recommendations are wrong when it comes to SQL Server.  Just because SQL Server has different needs than most applications that use a SAN.


SQL Server Partition Switch I have a partitioned table in SQL server 2017.
I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table.

I'm using the following query :

  ALTER TABLE original_table  SWITCH PARTITION 2 TO staging_table PARTITION 2

Normally this query finishes in no time (the partition contains ~ 12M records).

the issue is when there is at the same time a query which is inserting data to original_table , knowing that the new inserted data resides in a different partition that I'm trying to switch.
In this case , when I run the alter table query , it is locked by the insert query.

How to manage this issue ?

Not sure what you mean by "manage"... one of them has to wait for the other.

Imagine you need to change your tires or get an oil change, but your son took the car to the mall.

That is what is happening here. The metadata change has to wait for the data change to commit. You can control which has higher priority, though, by adding a wait option to the switch:

WITH
(
  WAIT_AT_LOW_PRIORITY
  (
    MAX_DURATION = n MINUTES,
    ABORT_AFTER_WAIT = BLOCKERS
  )
);

At the specified timeout you can choose to kill the blockers (BLOCKERS) or die gracefully (SELF). Number of rows in the partition is largely irrelevant (unless you are waiting for ~12M rows to actually update).

See Kendra's post here (do look through the comments) and this background post on low priority.


DBATools - pass through flat file configs to Agent Job Commands I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite.

Given $sa = Get-Credential and foo.config of the below form...

{
    "Schedule":                  "Foo",
    "Disabled":                  false,
    "FrequencyType":             "Weekly",
    "FrequencyInterval":         "EveryDay",
    "FrequencySubdayType":       "Time",
    "FrequencySubdayInterval":   0,
    "FrequencyRelativeInterval": "Unused",
    "FrequencyRecurrenceFactor": 1,
    "StartDate":                 "20180823",
    "EndDate":                   "20181023",
    "StartTime":                 "070000",
    "EndTime":                   "235959"
}

Attempting to use foo.config in a solution fails basic parsing with the following message:

~> $foo = Get-Content foo.config | ConvertFrom-Json
~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

  WARNING: [15:50:04][New-DbaAgentSchedule] A schedule was not provided! Please provide a schedule name.

Well that sucks... Especially since the following works just fine...

$bar = @{
    Schedule=                  "Foo"
    Disabled=                  $false
    FrequencyType=             "Weekly"
    FrequencyInterval=         "EveryDay"
    FrequencySubdayType=       "Time"
    FrequencySubdayInterval=   0
    FrequencyRelativeInterval= "Unused"
    FrequencyRecurrenceFactor= 1
    StartDate=                 "20180823"
    EndDate=                   "20181023"
    StartTime=                 "070000"
    EndTime=                   "235959"
}

New-DbaAgentSchedule @bar -ServerInstance "." -SqlCredential $sa

I really don't want to bother typing out each individual ... -Param1 $foo.Param1 -Param2 $foo.Param2 ... because I'm lazy. I would really much rather splat my config files into the various commands. Why won't this work!!?!1!

Well, 4 hours ago self... if you had bothered to inspect the types of $foo and $bar, you might not have wasted a whole afternoon swearing loudly in your open office floor plan as you scroll fruitlessly through the documentation...

~> ($foo | Get-Member).TypeName[0]
System.Management.Automation.PSCustomObject

~> ($bar | Get-Member).TypeName[0]
System.Collections.Hashtable

There's even a convenient, generalized solution on this very Q&A Network for converting a PSCustomObject into a HashTable.

$foo.psobject.properties | foreach -begin {
    $foo=@{}
} -process {
    $foo."$($_.Name)" = $_.Value
} -end {$foo}

~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

Easy peasy, lemon squeezy.

So splat away, ya big crazy. And when you're done feeling stupid, maybe share the workaround online in case it helps someone else. And if you're feeling super proactive, maybe consider setting aside some time to contribute to the module.


Btee-index select query takes long time My query is below:
WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime > ’2019 -06 -01 00:00:00’
                AND saletime < ’2019 -06 -30 23:59:59’
                AND centerid IN (708, 805, 432, 403)
                AND itemid IN (3, 6)
         ORDER BY
                saletime DESC limit 1000 offset 0
     )

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM   my_table_6 R
WHERE  R.id = ANY(
           SELECT Id
           FROM   CTE limit 1000
       )
       AND R.saletime > ’2019 -06 -01 00:00:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;

Explain (analyze,buffers) is below:

my_table_6 has 35 million records. It has a btree-index for the column Id.
When I select just 1000 records it takes almost 30 seconds.
How can I optimize it?
Note: PostgreSQL 12 server is running on Windows.
CTE query executed within 500 millisecond, but querying from my_table_6 takes more time. I have checked this many times.

It looks like you don't have a covering index for your CTE query on the my_table_6 table, so the PostgreSQL engine is doing its best by scanning the closest index it can find.
Create an index on the my_table_6 table that covers id and saletime, and then recheck the EXPLAIN ANALYZE is showing an index seek operation instead of index scan.
Also why not try re-writing your query in a more relational way like this, to see if you get a better query plan:
WITH CTE AS
(
     SELECT Id,
            SaleTime
     FROM   tbl_sales_log
     WHERE  saletime > ’2019 -06 -01 00:00:00’
            AND saletime < ’2019 -06 -30 23:59:59’
            AND centerid IN (708, 805, 432, 403)
            AND itemid IN (3, 6)
     ORDER BY
            saletime DESC limit 1000 offset 0
)

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM my_table_6 R
INNER JOIN CTE C
    ON R.id = C.id
WHERE  R.saletime > ’2019 -06 -01 00:00:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;


Hotel Booking Database design problem I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem.

Here's is the conceptual design:

And below are the tables of the relational layout so far :

GUEST
guest_ID varchar PK
email varchar
guest_password varchar
first_name varchar
last_name varchar
mobile_num varchar
member_status varchar
pref_language varchar
pref_currency_code varchar

HOTEL
hotel_ID varchar PK
hotel_name varchar
ratings_avg int
phone_num varchar
email varchar
currency_code varchar
street_name varchar
street_num varchar
zip_code varchar
city varchar
country varchar

ROOM
room_ID varchar PK
hotel_ID varchar FK to Hotel
room_name varchar
low_season_rate numeric
high_season_rate numeric
max_persons int

BOOKING
guest_ID varchar FK to Guest
room_ID varchar FK to Room
check_in date
check_out date
(a combination of the first 4 as PK)
persons_num int

PAYMENT
guest_ID varchar FK to Guest
room_ID varchar FK to Room
date_paid timestamp
amount numeric

EVALUATION
guest_ID varchar FK to Guest
hotel_ID varchar FK to Hotel
eval_date date
(a combination of the first 3 as PK)
rating int
guest_comment text

I came up with this design thinking as follows:
The guests will book rooms, which belong to hotels, pay for the rooms and then, if they want, after their stay, evaluate the hotel.

So I considered Booking and Payment as relationships between the Guest and the Room and the Evaluation as a relationship between the Guest and the Hotel.

The problem this design seems to have is that the Payment and Evaluation are completely cut off from the Booking, so their tables can fill even without a preexisting booking having taken place.

And the way I see it now, a Guest pays for a Stay(=Booking) in a Room and evaluates a Stay(=Booking) in a Hotel, so I am thinking that these tables should refer to the Booking table.

But Booking is a relationship, could I form a relationship between an entity and another relationship? Or could it be considered an entity?

I would welcome any thought about the subject.

I see nothing wrong with PAYMENT referring to BOOKING. Starting with BOOKING, why is check_out part of the P.K.? I don't see how you can check out of the room several times, for a check-in. Given:

CREATE TABLE BOOKING
( guest_ID varchar NOT NULL --  FK to Guest
, room_ID varchar NOT NULL -- FK to Room
, check_in date NOT NULL
,   PRIMARY KEY (guest_ID, room_ID, check_in)
, ... );

For PAYMENT you need to identify what BOOKING it relates to, so it makes sense to include check_in:

CREATE TABLE PAYMENT
( guest_ID varchar NOT NULL
, room_ID varchar NOT NULL
, check_in date NOT NULL
,     FOREIGN KEY (guest_ID, room_ID, check_in) REFERENCES BOOKING (...)
, date_paid timestamp NOT NULL
--    , amount numeric NOT NULL
);

I deliberately left out the P.K. for PAYMENT, if full payment is required, first 3 columns are sufficient, if partial payment is possible, date_paid can be included.

Other considerations, what is the agreed price of the booking? the price for a room may vary over the season, campaigns etc. If we assume that the price belongs to the booking somehow, the amount paid is only needed in case of partial payments, otherwise, a bill is either paid or not

For evaluation, is there a reason to include hotel instead of room? You can deduce the hotel from the room, and that would also make it possible to relate an evaluation to a booking as with payments.


If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`? My online-shop in PHP (OpenCart). On site we have product-search. For example product have name iSport 220, and if user search input string is iSport220 we must show this product. And vice versa.

First my thought was about LIKE operator, but I see that I need something different. In list of MySQL string functions I do not find any useful. What the best practice for this kind of problem? Version of MySQL is 5.5.

Replace space and compare:
... where replace('xxx yyy',' ','') = 'xxxyyy'

or generally it can be: ... where replace(column,' ','') = 'xxxyyy'

But not that this may not like your indexes on the columns.


SQL Server : how to decide if FULL backup or DIFFERENTIAL The following scenario (on SQL Server 2008 R2 and 2012 servers)

A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only checking for the existence of any row for that respective DB in the msdb.dbo.backupset, a differential backup has been initiated - a row has been found. It failed, because the DB was not the one matching the full backup.

Now, to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

Can anybody tell me if the following is a sensible thing to do:

SELECT
    sysdb.name
FROM
    sys.databases sysdb
INNER JOIN
    sys.master_files sysmf ON sysmf.database_id = sysdb.database_id
INNER JOIN
    msdb.dbo.backupset bck ON bck.backup_set_uuid = sysmf.differential_base_guid
WHERE
    sysdb.name = @dbname
    AND bck.database_name = sysdb.name
    AND bck.type = 'D'

Thank you very much in advance for a feedback,

Cheers

to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

The closest you can get is to use sp_SQLskillsDIFForFULL - from SQLSkills.com which will tell how much of the database has changed since the last full backup.

You can decide a threshold based on your company's backup policy and then have a job that executes a FULL or differential backup based on the threshold.

Remember that a FULL backup (without COPY_ONLY) will reset the differential base, so be cautious for any adhoc FULL backups taken and make sure that you test your restore policy to ensure that data loss is acceptable !

From Paul's blog

The basic idea behind the code is as follows:

For each online data file in the database
   For each GAM interval in the file
      Crack the DIFF map page using DBCC PAGE
      Interpret the DIFF bitmap to aggregate the changed extents
      Add the sum to the total changed extents for the database
   End
End
Report results


MySQL indexes maintenance I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries.

I am familiar with that formula that calculates the ratio between the max space available for a table VS the space used by data and indexes.

However my main questions are still unanswered. Perhaps this is due to the fact that I am familiar with index maintenance in SQL Server, and I tend to think that in MySQL it should be somehow similar.

In SQL server, you can have several indexes, and each one of it can have different levels of fragmentation. Then you can pick up one and perform a 'REORGANIZE' or 'REBUILD' operation in that particular index, without affecting the rest.

To the best of my knowledge, there is no 'table fragmentation' as such, and SQL Server doesn't provide any tool to fix the 'table fragmentation'. What it does provide are tools to check index fragmentation (understood like the ratio between the number of pages used by an index VS the fullness of that page and contiguity), as well as the internal and external fragmentation.

All of that is quite straightforward to understand, at least for me.

Now, when it comes the turn to maintain indexes in MySQL, there only exist the concept of 'table fragmentation, as mentioned above.

A table in MySQL can have several indexes, but when I check the 'fragmentation ratio' with that famous formula, I don't see the fragmentation of each index, but the table as a whole.

When I want to optimize the indexes in MySQL, I don't choose a particular index to operate on (as in SQL Server). Instead, I do an 'OPTIMIZE' operation in the whole table, which presumably affects all the indexes.

When the table is optimized in MySQL, the ratio between the space used by data + indexes VS the overall space is reduced, which suggest some kind of physical re-organization in the hard drive, which translates into a reduction of the physical space. However, index fragmentation is not only about physical space, but the structure of the tree that has been changed over the time due to inserts and updates.

Finally, I got a table in InnoDB/MySQL. That table has 3 million records, 105 columns and 55 indexes. It is 1.5GB excluding indexes, which are 2.1GB.

That table is being hit thousands of times ever day for updating, insertion (we don't actually delete records).

That table has been created years a go and I know for sure that nobody is maintaining indexes whatsoever.

I was expecting to find a huge fragmentation in there, but when I perform the fragmentation calculation as prescribed

free_space / (data_length + index_length)

it turns out that I have only a 0.2% fragmentation. IMHO that is quite unrealistic.

So the big questions are:

How do I check fragmentation of a particular index in MySQL, not the table as a whole
Does OPTIMIZE TABLE actually fix the internal / external fragmentation of an index as in SQL Server?
When I optimize a table in MySQL, does it actually rebuilds all the indexes on the table?
Is it realistic to think that reducing the physical space of an index (without rebuilding the tree itself) actually translates into a better performance?

Index fragmentation is much overrated.  Do not worry about it.

Two adjacent, somewhat-empty, blocks are merged together by InnoDB as the natural processing.

Random actions on a BTree cause it to naturally gravitate toward an average of 69% full.  Sure, this is not 100%, but the overhead of "fixing" it is not worth it.

SHOW TABLE STATUS gives you some metrics, but they are flawed -- "Data_free" includes certain "free" space, but not other "free" space.

There is unused space in each block; free 16KB blocks; free "extents" (nMB chunks); MVCC rows waiting to be reaped; non-leaf nodes have their own fragmentation; etc.

Percona and Oracle have different ways of looking at how big (number of blocks) an index is.  I find neither of them to be useful because of the limited definition of "free".  It seems that blocks (16KB each) are allocated in chunks (several MB), thereby leading one to believe that there is all sorts of fragmentation.  In reality, it is usually just most of one of these multi-MB chunks.  And OPTIMIZE TABLE does not necessarily recoup any of the space.

If SQL Server is using BTrees, then it is lying to say that there is "no fragmentation".  Think of what happens on a "block split".  Or think of the overhead of continually defragmenting.  Either way you lose.

Further note that a table and an index are essentially identical structures:

B+Tree, based on some index
The "data" is based on the PRIMARY KEY; each secondary index is a B+Tree based on its index.
The leaf node of the "data" contains all the columns of the table.
The leaf node of a secondary index contains the columns of that secondary index, plus the columns of the PRIMARY KEY.

If you have innodb_file_per_table = ON, you can clearly see the shrinkage (if any) after OPTIMIZE TABLE by looking at the .ibd file's size.  For OFF, the info is buried in ibdata1, but SHOW TABLE STATUS may be reasonably accurate since all "free" space belongs to every table.  Well, except for the pre-allocated chunks.

You may notice that a freshly optimized file-per-table table has exactly 4M, 5M, 6M, or 7M of Data_free.  Again, this is the pre-allocation, and the failure to give you the minute details.

I have worked with InnoDB for over a decade; I have worked with thousands of different tables, large and small.  I say that only one table in a thousand really needs OPTIMIZE TABLE.  Using it on other tables is a waste.

105 columns is a lot, but perhaps not too many.

Do you have 55 indexes on one table?  That is bad.  That is 55 updates per INSERT.  Let's discuss that further.  Keep in mind that INDEX(a) is useless if you also have INDEX(a,b).  And INDEX(flag) is useless because of low cardinality.  (But INDEX(flag, foo) may be useful.)

Q1: There is no good way to check for all forms of fragmentation in either the data or the secondary indexes.

Q2, Q3: OPTIMIZE TABLE rebuilds the table by CREATEing a new table and INSERTing all the rows, then RENAMEing and DROPping.  The re-inserting of the data in PK order assures that the data is well-defragmented.  The indexes are another matter.

Q4: You could DROP and reCREATE each index to clean it up.  But this is an extremely slow process.  5.6 has some speedups, but I don't know if they help with defragmentation.

It is also possible to ALTER TABLE ... DISABLE KEYS, then ENABLE them.  This may to a more efficient rebuild of all the secondary indexes at once.


Why Do My Nonclustered Indexes Use More Space When I Delete Rows? I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.

I wrote a query against dm_db_partition_stats to report the difference (after - before) in pages:

Index 1 is the clustered index, Index 2 is the primary key. The others are nonclustered and non-unique.

Why are the pages increasing on those non-clustered indexes?
I expected the numbers to at worst stay the same.
I do see performance counters report an increase in page-splits during the delete.

When deleting, does the ghost record have to move to another page?
Does this have to do with "uniqueifiers"?

We are in the middle of rolling out RCSI, but right now, RCSI is off.

It is a primary node in an availability group. I know that snapshot is used somehow on secondaries. I'd be surprised if that was relevant. I plan to dig into this (looking dbcc page output) to learn more. Here's hoping someone has seen something similar.

One possible scenario that very much amuses me:

The rows were originally written when the database didn't have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
RCSI or SI was enabled, or the database was added into an Availability Group
During the deletions, a 14-byte timestamp was added to the deleted rows to support RCSI/SI/AG reads

Since this server is a primary in an AG, it's affected just like the secondaries are. The version info is added on the primary - the data pages are the exact same on both the primaries and secondaries. The secondaries leverage the version store to do their reads while the rows are being updated by the AG, but the secondaries don't write their own versions of the timestamp to the page. They just inherit the versions from the primary's work.

To demonstrate the growth, I took the Stack Overflow database export (which doesn't have RCSI enabled) and created a bunch of indexes on the Posts table. I checked index sizes with sp_BlitzIndex @Mode = 2 (copy/pasted into a spreadsheet, and cleaned up a little to maximize info density):

I then deleted about half of the rows:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

Amusingly, while the deletes were happening, the data file was growing to accommodate the timestamps, too! The SSMS Disk Usage Report shows the growth events - here's just the top to illustrate:

(Gotta love a demo where deletes make the database grow.) While the delete was running, I ran sp_BlitzIndex again. Note that the clustered index has less rows, but its size has already grown by about 1.5GB. The nonclustered indexes on AcceptedAnswerId have grown dramatically - they're indexes on a small value that's mostly null, so their index sizes have nearly doubled!

I don't have to wait for the deletion to finish to prove that out, so I'll stop the demo there. Point being: when you do big deletions on a table that was implemented before RCSI, SI, or AGs were enabled, the indexes (including the clustered) can actually grow to accommodate the addition of the version store timestamp.


Performance issues moving 1 million rows between tables I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:

delete from table1 OUTPUT *.delete into table2

I am executing this job every hour, but it takes a long time.

Another problem is that my log file size is continuously increasing. How can I fix this?

Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal.

If you're using Enterprise edition, take advantage of table partitioning. If there's only a single partition in a table, that partition is the table. Use ALTER TABLE ... SWITCH to "move" the data into table2.
Rename table1 to table2 using sp_rename, then recreate table1 using a SELECT TOP 0 * INTO statement, or by using the original table definition. Note that if there are any constraints/indexes/etc., you'll need to rename those, too, to avoid name clashes when the new table is created again. This method works with any edition, so I might actually recommend it over the other solution, which is Enterprise only.

Note that there are some restrictions to doing either of these -- see the documentation I've linked to. I'm not sure if you tagged the question with replication because the table is replicated (doesn't make much sense), or because you're trying to "replicate" the data (also doesn't make sense as you're wanting to move data, not make a copy, which is what replication implies).


Using SQL ORDER BY for complex criteria Let's say my relation is like so

and I wanted to sort by rank, but A's and B's first. So my relation will look like

At first I tried to just have two relations, where I order both of them by rank, then union the two relations, but they just end up getting jumbled together. I've also tried using cases, but I don't understand how it works. I kept getting errors about mixing characters with integers.

Edit: Forgot to mention that the first relation above is what I end up with after a somewhat complex number of views. For example, I created two views, one which only has A and B classes, then I ordered that by rank. Second view just C classes, ordered that by rank. Then I unioned them, but they just get mixed.

You only need a CASE expression in the ORDER BY, to divide the rows into two groups, so the ones with class 'A' or 'B' are put in first group (1) and the all the others in the second (2):

ORDER BY
    CASE WHEN class IN ('A', 'B') THEN 1 ELSE 2 END,
    rank ;


Will frequent delete and inserts of the same data lead to fragmentation? I have a table which caches images from another data source:

CREATE TABLE [dbo].[ProductImages] (
    [ProductId] INT NOT NULL ,
    [ImageId] INT NOT NULL,
    [Image] VARBINARY(MAX) NOT NULL,
    PRIMARY KEY ([ProductId], [ImageId])
)

The images are served together with [Product] data in the form of:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Now, the problem is that the images from the data source may sometimes be updated, and there is no trigger-like or event-like mechanism to detect this change. So, my solution is to have a scheduled job which periodically downloads images from the source. Since the images are pre-processed (format changed, resized) before being stored into the database, I have no way of actually knowing whether the image has changed. Therefore, every time I do the following:

DELETE FROM [ProductImages] WHERE [ProductId] = 123
INSERT INTO [ProductImages] VALUES (123, 0, {Image_0})
INSERT INTO [ProductImages] VALUES (123, 1, {Image_1})

Most of the time (say at least 98%), there has been no change - I am just deleting and re-inserting the same data for nothing.

If this scheduled job is executed long enough, will it eventually lead to poor table performance because the data is scattered all over the place?

Let's consider what data will actually be stored on the data pages. You said that your images are around 10 KB - 17 KB which means that the VARBINARY(MAX) column will never be stored in row. Instead, it will always be stored as LOB data. So on the in row page for each row you'll have two INT columns along with a 24 byte pointer to the LOB data. Assuming a generous overhead of 10 bytes per row you can fit around 8000 / (10+4+4+24) = 190 rows on a single page. The row data on each page may be physically out of order and the different data pages may not be in order but SQL Server won't just insert the rows wherever it can find space. It's not true that for a given ProductId you'll have ImageId 1 stored on page 1, ImageId 2 stored on page 2, ImageId stored on page 3, and so on. So when you run the following query:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Unless you have hundreds of images for one product, all of the IO is going to be from going to the LOB pages to get the [Image] data. There will only be one or two relevant pages that store the pointers to the LOB data. I don't know how SQL Server picks which page is used for the LOB data. I suppose it's possible for the LOB pages to become more spread out over time.

No one here can say for sure if fragmentation will be a problem for you. As I see it your options are to schedule a maintenance job that deals with any fragmentation, to add code to your application to prevent unnecessary updates, or to simulate your workload to see if you need to take further action. From SQL Server's point of view it doesn't matter that the VARBINARY data corresponds to an image. Anything that you store to the column of the right size should work fine for a test.


Breaking down SSIS keystring values for connection manager between Server2000 and Server20008 I have an SSIS package between a SQL2000 and SQL20008 server that cannot connect since migrating to VM. The SSIS package runs a data transfer between the two servers using 3 Connection manager strings. I am trying to understand what the values of the connection strings mean. I am not a SQL DB admin/tech at all. The 3 strings are below.
Some of the values seem self-explanatory, others I do not understand.
Data Source=1.1.1.1;
User ID=SQLadm;
Initial Catalog=SQL_db;
Provider=SQLNCLI10.1;
Persist Security Info=True;
OLE DB Services=-13;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}1.1.1.1.SQL_db.SQLadm;

Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E92CF7CB-23A5-4713-8593-F2FF92C980EA}localhost.Server2008;

Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Connect Timeout=10;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E28D4B65-CE56-4BD7-BEC4-02EE1738F568}localhost.Server2008.SQLadm;

Specifically what do the following mean:
Provider=
OLE DB Services= yes, object linking services and I read the MS Doc here( https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms717922(v=vs.85)#:~:text=OLE%20DB%20provides%20common%20services,performance%20of%20OLE%20DB%20providers.&text=(OLE%20DB%20services%20cannot%20be,see%20Creating%20Data%20Source%20Objects.) but hoping someone can dumb it down for me.
Also what do the 32 digit codes in {} mean? Is it a hash check/authentication or ID for some process?
And for Application Name= this seems redundant. I am already running the transfer package, why does the name and the connection parameters (IP.DB.USER) need to be repeated at the end?
Thank you for any input or resources I can reference! I am not a DB/SQL admin in any fashion.

the provider is the driver name. A connection can be opened against variuos sources and destination (SQL server, Oracle etc..). You have to specify the proper driver.
SQLNCLI10.1 is the sql server native client version 10.1
OLE DB Services=-13; is the default option for additional features like pooling and recursion etc...
Application Name is only a string that can be usefull to distinguish the running workload from other applications. Most of times you can find ".NET application" or the ssis dtsx name or id like in this case.


Xquery Speedup/Optimization I am using Sql server 2008 R2. To speed up the query execution in sql i have changed xquery part of it.I Have used cast method to query xml column and i got following results.
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`' OR [xml_column_name].exist('for $x in /Root/Row where( ($x/xml_column_name[fn:contains(upper-case(.),fn:upper-case("94484"))]/text()) )  return $x')>0 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:
                                Trail4   Trial 3  Trial2    Trial1        Average(ms)
  Client processing time            2       3        3          4           3.0000
  Total execution time              2059    1657    1745        1608        1767.2500
  Wait time on server replies       2057    1654    1742        1604        1764.2500

New Query:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`'
 OR CAST (xml_column_name as varchar(max)) like '%94484%'
 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:
                               Trail4   Trial 3   Trial2    Trial1     Average(ms)
  Client processing time            4       3        2          3       3.0000
  Total execution time              314     371      230        479     348.5000
  Wait time on server replies       310     368      228        476     345.5000

Whether it is a proper way to speedup query? If not, is there any other method to improve the execution speed?

This is another example that demonstrates how Relational Databases are really good at finding lots of small bits of data and putting them together, but quite rubbish at taking large slabs of data and pulling them apart again.
Xml querying functions go some way towards improving this but by far the best way is to move the data that you'll be filtering by (i.e. that appears in where clauses in your queries) into old-fashion table fields and properly index them.  You already have a separate Phone Number field - make sure that your application code updates that when storing this data.  You might also consider triggers if you're worried about people and/or other applications changing the data by "other means".
Casting the field from XML into text completely defeats the point of holding this data as XML at all.  If that sequence of character were to appear anywhere in the entire XML structure then that record would be returned - potentially lots of "false positives".
Why is it faster with the cast?  The DBMS doesn't have to go through all the [relatively slow] XML processing and can "just" scan through the entire table, doing a [relatively quick] "string" search on the cast'ed field.
Why a table scan?  Well, you have a leading wildcard on that criteria, which will effectively prevent the DBMS from using any indexing on that field.  Expect your query performance to degrade horribly as the table grows in size.


What is a good script or free/trial tool to inventory/replicate all settings in master db? Context: CU10 failed, reporting a corrupt master db when applied to SQL Server 2012 SP3 CU5.  We built an empty new master on that server and will restore the backup of the CU5 master into a new CU5 instance on a different server. Now we need to replicate all settings from the restored CU5 master into the newly built CU10 master.

We cannot restore the backup of the CU5 master onto the CU10 instance of course, because the backup is from an older version.  We are not concerned about master encryption key as we have not used SSRS, SSL or encryption on this server.

Fastest way...
1. Connect to instance with CU5 master
2. Right-click master database> tasks> Generate Scripts
3. Wizard is launched for scripting database objects.
To get server principals:
How to transfer logins and passwords between instances of SQL Server

Or, Powershell!
Automated Script-generation with Powershell and SMO


Can I lose data if I use InnoDB? During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.

First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko)

Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled.
There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure.
Double Write Buffer
A record of all data and index pages that are supposed to be written back to the physical files of InnoDB table are written in the Double Write Buffer beforehand. The Double Write Buffer is contained with the system tablespace, better know as ibdata1. That way, in the event of a crash, the InnoDB Storage Engine has something to refer to during crash recovery. To ensure that the Double Write Buffer's pages are there for you after a MySQL crash, always leave innodb_doublewrite set to 1 (default) and do not start mysqld with --skip-innodb_doublewrite.
Transaction Log Flushing
The Log Buffer is used to collect changes to be written to the Transaciton Log Files (ib_logfile0, ib_logfile1). The default flushing behavior (innodb_flush_log_at_trx_commit = 1) is to  flush the contents of the InnoDB log buffer to the log file at each transaction commit and the log file flushed to disk. Some change innodb_flush_log_at_trx_commit to 0 or 2 to increase InnoDB write performance. However, changing innodb_flush_log_at_trx_commit to anything other than 1 makes it possible to lose 1 second's worth of transaction upon an OS crash or power outage.
Your Comment
The only way to see data that is not committed would be to start your transactions with READ UNCOMMITTED isolation and never run COMMIT. You should always commit your transactions. Days of rollback info sounds like an abuse of InnoDB, your data, and your application.
SUMMARY
Although these are the default settings, you should make sure these settings are in place
[mysqld]
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
auto_commit = 1


What encryption options are available in SQL server standard edition? I'm trying to look into what kind of encryption I can do with SQL server standard edition from 2008 and later.

So far, I know that I can't use Transparent Data Encryption (TDE) as that's part of Extensible Key Management (EKM).

When I look up this topic I'm getting the result that column encryption is possible, but then I also find that cell level encryption is sometimes referred to as the same thing and that it is also part of EKM.

These are the sources from which I'm getting the contradictory information:

http://web.townsendsecurity.com/bid/72311/Encryption-Options-for-Microsoft-SQL-Server

http://www.experts-exchange.com/questions/27949443/Encryption-feature-of-SQL-server-2008-R2-standard-edition.html

Any clarity on this subject would be very helpful.

If you have SQL Server Standard edition then you do not have Encryption built into the product.  You have other options as the articles you reference mention, but they require work on your part to implement.

I think the confusion is where you are seeing that you can implement column/cell encryption using Enterprise edition OR through alternative methods.  Those alternative methods are:

Triggers and CLR functions to do the encryption/decryption for you.
Using the application layer to encrypt/decrypt the data on read and
write to/from the database.

The advantage to using TDE is that SQL understands the encryption and you can search on the encrypted data directly because it is only encrypted on disk.  But if you have encrypted the data using CLR or the application then you cannot do that and must decrypt every row.

Encryption is hard, don't roll your own.  Find out what is required by the business, not managements "everything must be encrypted" and work towards that.  You may be able to get away with seeing if the storage layer has encryption that can be implemented.


Best way to automate running several MySQL commands We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see.

We created these views in a new db specific to the new user.  Creating all of these views requires running several mysql queries.

I would like to automate this process into a script, but feel there must be tools already out there that take a list of queries and run them.

Can anyone recommend any such tools or even any built-in features in mysql that could assist me here ?

Thanks!

Create a text file containing all your commands. Call it newuser.sql. Then when you want to set up a new user, let's call him gaius, you would simply do

$ cat newuser.sql|mysql --user=gaius --password=abc123 somedb

Obviously you could even create new users in a script, let's call it newuser.sh

#!/bin/bash

echo "create user '$1'@'%' identified by password('$2');"|mysql --user=root --password=secret
cat newuser.sql|mysql --user=$1 --password=$2 somedb

Then do

$ ./newuser.sh gaius abc123


Shell script - psql run multiple statements from a variable one by one in one transaction Im preparing a shell script to generate analyze command and all the commands will go to a variable.

echo $analyze_query

analyze tbl1;
analyze tbl2;
analyze tbl3;

Now I want to run one by one, and Im able to achieve that by using the below command.

for $i in $analyze_query
do
psql -h host -U user -d db -c"$i"
done

But this query will close and open a new connection for every command, Is there any way that I can run the one by one but in one session?

Yes, we can do it.

echo the variable and then pipe it to psql.

echo "$analyze_query"  |  psql -h host -U user -d db

Use () to group the command output then pipe it to psql.

(for i in $analyze_query
do
 echo $i
done ) |  psql -h host -U user -d db


How to -create- performance issues? - oracle I want to dedicate some time to learn more about performance and tuning issues.
I assign for that a clean DB. I want to know how can I load into it some data and performance-problem queries/DML/DDL?
Do you know some scripts that can cause/generate that (purposely or not)?

The idea is to learn the basic(and maybe more) of em/awr/addm etc - tuning and performance wise. I dont really know what specific to ask, but I'm guessing index problems, selects issues, wrong way to access the data, etc..

Books are great but I have the knowledge I need some actual work on that.

Maybe its not a Q&A at all, but for me any answer (as long as it is an answer to my question) will do.

EDIT
I dont want just a server load script - because thats has no point, plus thats not what I want.

This is a pretty broad topic that would largely depend on your specific needs.

Here is a link to a fairly recent article about load testing tools.   It provides pros and cons of several different options:

http://www.pythian.com/blog/oracle-database-load-testing-tools-overview/

Personally, I like the Silly Little Oracle Benchmark (SLOB).  http://kevinclosson.wordpress.com/2013/05/02/slob-2-a-significant-update-links-are-here/


How to delete SQL Server Database Mail history WITHOUT stored procedures This is really straight forward. The documented way to delete Database mail history in SQL server is to use the following two stored procedures:sysmail_delete_mailitems_sp and sysmail_delete_log_sp

Great. But what happens when apparently billions of emails are stored in here taking up several hundred gigabytes in msdb, and even the process of trying to clear only 100 days worth of email takes hours or days?

I don't want any history and I don't want to wait until I'm a senior citizen to clear the list of e-mail history. How do I truncate this history in one quick transaction?

So far my efforts at using the built in procedures have been futile - growing tempdb and msdb to terabytes in size and sitting "executing" for hours on end. Any attempt to cancel it results in the entire transaction being rolled back. It so far hasn't even been possible to count the number of emails that exist in the history - it's that bad.

One idea could be:

script out CREATE TABLE for sysmail_attachments, sysmail_send_retries, sysmail_mailitems, and sysmail_log
start a transaction
drop the tables in that order
create the tables again using the script from 1.
commit the transaction
create a job that purges those tables regularly (like, once an hour, from the sounds of it), also in that order


Converting Non Unicode string to Unicode string SSIS I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error stating Output column and column "A" cannot convert between unicode  and non-unicode string data types.

To fix this I added a data conversion component and converted all the columns to

"Unicode String [DT_WSTR]"

and I no longer received the error. The only problem is that I had about 50 columns where I had to go 1 by 1 and select "Unicode String [DT_WSTR]" from the drop down list. I then had to go into the destination component and map the newly converted columns to my excel file.

My question is, if anyone else has come across this, is there a better more efficient way to get around having to do all the manual data type conversions? Having to convert and map all the columns one by one doesn't seem to practical especially if you have a large number of rows.

I understand excel files are not the best way to go for importing and exporting data but it is what is required in this particular case.

I might look for a way to just export to a flat text file and then try to convert to excel as a last step in the package. I'm hopping this wont trigger the same unicode / nonunicode conversion error.

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...


Postgresql 9 speeding up indexed inserts (JPA) I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a good performance (around 25k inserts per second) with multi-threading and batching of inserts every few thousand inserts or so, completing a whole run in around 8mins.

However, I noticed that I had a few of the foreign keys which had an index missing, which I would really wish to have both from an analysis point of view to drill down in the data, and also to delete data to a specific run. Unfortunately when I added in these 3 indexes to the table that is getting most inserts, performance dropped down drastically to around 3k per second.

Is there any way to avoid this performance slow down? I know that one option is to drop the indexes before a run and recreate them in the end. Another more clumsy option is to generate the data of the biggest table in a file instead and use COPY. I guess I can only do it on the largest table in the relation, due to the foreign key values which I would need to know (generated through sequences).

Both alternatives seem to be hacks. Is there any other solution, maybe a bit less intrusive on the application? Some setting to tell postgres to defer indexing or something of that sort?

Any ideas welcome.

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables.

I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad.

Tune the storage parameters of your tables and indexes

Besides adding several indexes to the database, I changed the storage parameters of some tables and specified a custom value for the fillfactor of the table itself and the indexes.

Setting a custom value for the fillfactor allows you to instruct PostgreSQL how much space in each page should be reserved for further updates. The same applies to indexes.

See the documentation on CREATE TABLE and the description of the available storage parameters for details.

Monitor your infrastructure

Monitor and analyze your infrastructure. The PostgreSQL wiki lists a lot of usefull tools.

Find long running statements

Enable statement logging by altering the following values in your postgresql.conf file:

log_min_duration_statement=x to log all statments which run longer the x milliseconds
log_min_messages=level to a level what helps you the understand the statements generated by JPA

See the description of the runtime logging configuration for details

Install pgFounine to analyse your PostgreSQL log file easily.

Be picky

Besides altering the storage parameters I also gained a lot of performance by optimizing all frequently executed statements. In parts I won only 100 or 50 milliseconds for each execution but in total I gained more then 5 seconds for complex operations.


Postgresql return results of dynamic SQL I'm using Postgresql, version 12.

My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that the form of the table which is returned is not known in advance.

This is a follow up question to a previous thread , in which I learned how to dynamically create a table within a function with a lot of help from the StackExchange community. But now I realize it would be much nicer, instead of creating a new table inside the function, to just return the result of the select.

The actual problem I need to solve is as follows (this is repeated from the previous thread):

Say I have a table with the following information:

Table nominal_dollars:

year       GDP        Dividends
2000      100              20
2001      110              30
2002      120              40

In the above table, the numbers are not adjusted for inflation. A common procedure I want to do is create a new table, in which the numbers are adjusted for inflation. This will mean I need to join the above table with a new table, the consumer price inflation (CPI), which has the following form

Table cpi:

year       cpi_adjustment
2000      1
2001      2
2002      3

This will allow the creation of a new table, in which all of the numbers are adjusted for inflation, i.e. the original numbers are multiplied by the cpi adjustment:

Table nominal_dollars_adj:

year       GDP        Dividends
-----------------------------------------
2000      100              20
2001      220              60
2002      360              120

Where GDP  for the new table equals GDP from the original table * cpi_adjustment, and the same for dividends.

Now, I want to do this CPI adjustment procedure for many different tables --- which may have different numbers of columns.

The answer which was given by is Erwin is really nice (see below), and generates a new table dynamically. But my question remains --- instead of creating the new table, can I return the results in a select?

CREATE OR REPLACE FUNCTION f_results_inflation_adjusted(_tbl text)
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE for debugging
   EXECUTE (
   SELECT format('DROP TABLE IF EXISTS public.%1$I;
                  CREATE TABLE public.%1$I AS SELECT %3$s
                  FROM public.%2$I t JOIN public.cpi c USING (year)'
               , _tbl || '_adj'     -- %1
               , _tbl               -- %2
               , string_agg(        -- %3
                  CASE a.attname
                     WHEN 'id'   THEN 't.id'    -- case sensitive!
                     WHEN 'year' THEN 't.year'
                     ELSE format('t.%1$I * c.cpi_adjustment AS %1$I', a.attname)
                  END, ', ' ORDER BY a.attnum
                 )
          )
   FROM   pg_catalog.pg_attribute a
   JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
   WHERE  c.relname = _tbl
   AND    c.relnamespace = 'public'::regnamespace
   AND    NOT a.attisdropped
   AND    a.attnum > 0
   );
END
$func$;

You can create a function that returns a table, here is an example.


Summarize from unlimited level depth parent-child in mysql I have 2 table master coa and coa transaction in MySQL like this,

master :

 id | coa  | id_parent |
----+------+-----------+
  1 | 1    |  NULL     |
  2 | 11   |   1       |
  3 | 111  |   2       |
  4 | 12   |   1       |
  5 | 121  |   4       |
  6 | 122  |   4       |

transaction :

 id | debit | credit |
----+-------+--------+
  3 |     0 |      5 |
  3 |     0 |     20 |
  5 |    10 |      0 |
  5 |    15 |      0 |
  5 |     5 |      0 |
  6 |     0 |      5 |

The depth level child-parent is unlimited. What i want to get is view like this,

 id | coa  | debit | credit |
----+------+-------+--------+
  1 | 1    |    20 |     30 |
  2 | 11   |     0 |     25 |
  3 | 111  |     0 |     25 |
  4 | 12   |    20 |      5 |
  5 | 121  |    20 |      0 |
  6 | 122  |     0 |      5 |

How can i accomplish this using SELECT ? so far i only can get debit credit only the lowest depth using GROUP SUM and join. Any suggestion?

The following query:

SELECT  m1.id, m1.coa, IFNULL(sum(t.debit), 0) as debit,
        IFNULL(sum(t.credit), 0) as credit
FROM master m1
JOIN master m2
    ON m2.coa like CONCAT(m1.coa, '%')
LEFT JOIN transaction t
    ON m2.id = t.id
GROUP BY m1.id;

Returns the following for me:

+----+------+-------+--------+
| id | coa  | debit | credit |
+----+------+-------+--------+
|  1 | 1    |    30 |     30 |
|  2 | 11   |     0 |     25 |
|  3 | 111  |     0 |     25 |
|  4 | 12   |    30 |      5 |
|  5 | 121  |    30 |      0 |
|  6 | 122  |     0 |      5 |
+----+------+-------+--------+
6 rows in set (0.00 sec)

Please note that all transactions are or have 1 as parent, so I am not sure why you didn't add up your third transaction.

My query returns 1 row for each id, even if it has not transactions (with credit and debit 0). Change the LEFT JOIN to a JOIN and get rid of the IFNULLs if you only want ids with movements.

Also please be aware that this query will be very badly improved in performance with indexes, and that I would recommend rethinking an alternative structure if the query is frequent and any of the two tables is very tall.


How to keep updates (diffs) of some entity in the database What is the best way to keep updates (diffs) of the some entity in the database? At StackOverflow we can edit questions and answers. And then we can look at any revision of the question or answer we want. For example:  revisions of some random question. Maybe someone knows how it realized in StackOverflow?

To be clear in my case I have some entity (article) with some fields (name, description, content). Many users can edit the same article. I want to keep history of the article updates (something like version control) and I want to keep only diffs, not the whole content of the updated article. By the way I use PostgreSQL, but can migrate to any other database.

Here is some requirements. You don't need to fully satisfy them. But if you do it will be much better. Nevertheless any answer is much appreciated. So I want to have ability:

to keep only diffs to not waste my space for no purpose.
to fetch any revision (version) of some article. But fetching last revision of the article must be really quick. Fetching speed of other revisions is not so important.
to fetch any diff (and list of diffs) of some article. Article can have changes in fields: header, description or content (like StackOverflow have changes in header and content), so it must be taken into account.

Check out Drupal and RDF.  Check out what StackExhange (etc) uses.  And study how GIT works.

I recommend keeping the current value in the primary table, and put the history in other table(s).  That is, plan for efficient access to the current value, at the expense of history.

If you are talking about "articles" of, say, 1K characters, then compress them.  Compress old copies; optionally compress the 'current' copy.  But do the compression in the client, so as to offload the database server.  This will save space.  If you are talking about ancillary data (dates, names, etc), don't bother compressing.

Do not save the entire set each time there is a change.  That is, if you change the date, don't store another, identical, copy of the article body.  Too bulky.

You mentioned diffs -- Do you intend to do diffs on paragraphs?  If so, do you have a good algorithms for such?  (My previous comments do not assume "diff".)

Oh, well, I guess I am not adding much to your stated requirements.


Full-text Catalog Corruption I have a SQL Server 2005 database that I backed up and restored onto a new server running SQL Server 2012. So far so good. This morning I had a really bad power outage and the new server went down. When the new server came back online the database had Recovery Pending status.

I put the database into single user mode, did a DBCC CHECKDB and then put the database back into multi user mode and the Recovery Pending status went away. When testing the backup jobs I noticed there was an issue with the full text catalog.

I tried to drop the catalog and recreate it, but it will not do it. It complains that it is not empty and that it has a full text index. I tried removing the index from the table that is using the full text catalog, but then I got a message saying the text catalog is empty, read only or offline.

What am I missing?

Note: The database is online, but the backup jobs keep failing. When I do a DBCC CHECKDB I get the following:

Msg 8996, Level 16, State 1, Line 1
IAM page (3:9) for object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) controls pages in filegroup 0, that should be in filegroup 2.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:9) is pointed to by the next pointer of IAM page (0:0) in object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data), but it was not detected in the scan.
Msg 8968, Level 16, State 11, Line 1
Table error: IAM page (3:9) (object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data)) is out of the range of this database.
Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) due to invalid allocation (IAM) page(s).
CHECKDB found 2 allocation errors and 2 consistency errors in table 'sys.fulltext_avdl_1062555119' (object ID 983374968).
CHECKDB found 2 allocation errors and 2 consistency errors in database 'MY-DATABASE'.

Looking at your errors, especially 8996, looks like a hardware problem.

Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows system and application logs and the SQL Server error log to see whether the error occurred as the result of hardware failure. Fix any hardware-related problems that are contained in the logs.

CHECKDB wont be able to repair this error. Best is to restore from a GOOD backup.

Refer here for more details.


How to create a synonym for a column in SQL Server? The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this?

CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId]
GO

select ProductTest from dbo.Product

If you want to retain the table name but refer to an existing column by a new name a computed column will do this for you. The computed value of the column will be just the value of the existing column.

alter table [dbo].[Product]
add [ProductTest] as ([ProductId]);

Now you can write

select
    [ProductId],
    [ProductTest]
from [dbo].[Product];

The two columns will have the same value, always.

If you reference only the computed column my guess is there won't even be a run-time overhead.


Efficient way to DELETE whole partition's data in table partitioned by HASH(DAY(datetime))? I have a MySQL InnoDB table PARTITIONed by HASH(DAY(dateTime)).
I'd like to remove partitions holding data older than 10 days.

CREATE TABLE `partitioned` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dateTime` datetime NOT NULL,
  `sourceId` int(10) unsigned NOT NULL,
  `destinationId` int(10) unsigned NOT NULL,
  `times` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`dateTime`),
  UNIQUE KEY `key` (`dateTime`,`sourceId`,`destinationId`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY HASH(DAY(`dateTime`)) PARTITIONS 31

I've tried some queries:

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE DAY(`dateTime`) IN (1,2,3,4);
EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` < '2015-01-01';

EXPLAIN for those two is showing that all partitions will be inspected. Not good!

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` IN ('2015-01-01','2015-01-02','2015-01-03','2015-01-04');

EXPLAIN for this one is showing that only required partitions will be inspected. I suppose that DELETE will also touch only required partitions, right?

Questions are:

So, what is the fastest available way to DELETE from table
partitioned by HASH?
Is it possible to get name of partition holding
DAY? Which function is MYSQL using for partitioning using
HASH(DAY())?

Or it is a bad idea and I should move to RANGE partitioning with DROPing and CREATing partitions on daily basis?

UPD: I see http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html that for MySQL >= 5.5.0 it is possible to TRUNCATE PARTITION, but what if I have AUTO_INCREMENT field in partitioned table? Is TRUNCATE PARTITION reseting auto_increment field or what?

UPD2: I'll try to answer my question.
Ok, we can get name of partition by EXPLAIN PARTITIONS SELECT * FROM partitioned WHERE dateTime IN ("2011-01-01"). As soon as we know name of partition, we can do ALTER TABLE partitioned TRUNCATE PARTITION {partitionName}, right? Is it a good way, what do you think?

To answer the update, the TRUNCATE PARTITION will not reset the auto_increment field. This makes sense since it would be sticky to handle in relation to the other partitions in the auto increment scenario.

However, a table truncate will reset the auto increment key.


Can you help clear confusion of ORACLE Timestamp with Local Time Zone? Below is a description of ORACLE TIMESTAMP WITH LOCAL TIME ZONE

  ...data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone.

What does data stored in the database is normalized to the database time zone means ? Normalized...how ?

If it is normalized, why isn't it returned accordingly with Server's time zone instead of the users' local session time zone... How is it "session" and not "server" ?

Normalized means that the timezone information is not stored. When storing such data, the timestamp value is automatically adjusted to be in the same time zone as the database server. When retreiving such data, the timestamp value is automatically adjusted to be in the same time zone as the client.

For example, I simulate that my client is in a different time zone:

$ export TZ=America/Los_Angeles
$ sqlplus user/password@orcl

Create a table to store timestamp values:

SQL> create table t1 (t timestamp with local time zone);

Table created.

Now notice how my local (client) time is different (current_timestamp) than the server time (systimestamp):

SQL> select current_timestamp, systimestamp from dual;

CURRENT_TIMESTAMP                   SYSTIMESTAMP
----------------------------------- -----------------------------------
29-DEC-16 06.36.09.364628 AM -08:00 29-DEC-16 03.36.09.364623 PM +01:00

Now I insert my local time:

SQL> insert into t1 (t) values (current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 06.36.32.100571 AM

Just what I expected. But if I examine the real contents:

SQL> select dump(t) from t1;

DUMP(T)
----------------------------------------------------
Typ=231 Len=11: 120,116,12,29,16,37,33,5,254,151,120

TIMESTAMP is stored as:

1st byte: First two digits of the year: 20 + 100 = 120
2nd byte: Last two digits in the year: 16 + 100 = 116
3rd byte: Month: 12
4th byte: Day: 29
5th byte: Hour: 15 + 1 = 16
6th byte: Minute: 36 + 1 = 37
7th byte: Second: 32 + 1 = 33
8th-11th bytes: fractions of second

The timezone information would be in the 12th and 13th byte, but it is not stored with this datatype.

So the actual data stored is 2016-12-29 15:36:32:..., because my database is in the CET timezone.

If I pretend to be in another different timezone, my query will return a timestamp adjusted to that timezone:

$ export TZ=Asia/Hong_Kong
$ sqlplus user/password@orcl

SQL> select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 10.36.32.100571 PM


An uncommitted transaction updates a column; how can a different transaction see the old column value? Let's assume the following case.

There is a students table:

id | name
-----------------
1  | Mateus

A "User A" executes the following statement without committing the transaction:

update students set name = 'Gustavo' where id = 1

Then a "User B" executes the following query:

select * from students with (nolock) where id = 1

User B will get the name Gustavo, that's the expected behavior. The DB will return the uncommitted value because of the with(nolock) instruction.

Is there a way to get the old name, Mateus (the previously committed value), even when there's an uncommitted transaction?

You can use Read Committed Snapshot Isolation (RCSI) on your database, which uses space in TempDB to keep track of committed versions of the data.

Be aware that RCSI adds 14 bytes to every versioned row, so you might see some additional page splits in the database. It also requires that TempDB's space and I/O be monitored to make sure it's not slowing things down.

Read more by Paul White and in this question.


DBCC CHECKDB returning space issue error My database in Production has 28ndf files in different Drives.And on T drive test_audit20.ndf to test_audit23.ndf files only. and file test_audit20 and test_audit21.ndf autogrowth is none.
 While executing checkdb command on test_audit db I am getting error :

  Msg 1823, Level 16, State 6, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 1823, Level 16, State 7, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 1823, Level 16, State 8, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 7928, Level 16, State 1, Line 1
  The database snapshot for online checks could not be created.
  Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams.
  Attempting to get exclusive access to run checks offline.
  Msg 8921, Level 16, State 3, Line 1
  Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
  Msg 3313, Level 21, State 1, Line 1
  During redoing of a logged operation in database 'test_audit', an error occurred at log record ID (19372:991854:10).
  Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
  Msg 9001, Level 21, State 7, Line 1
  The log for database 'test_audit' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
  Msg 5128, Level 17, State 2, Line 1
  Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.
  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

I am not sure why it would need to write to this disk.

It could be that auto-grow is turned on for one of the files on this disk.  We could turn that off and it might begin working .

Currently I have X and Y drives that are the “active” data drives, where growth should happen.  Those are also almost full (3TB each) and I have to stand up some new disk drives and consolidate the data files to them.  I have hit our limit with AWS for provisioned terabytes in this region.

Additionally, I have been manually extending these data files every week.  I cannot rely on SQL’s auto-grow as I have mentioned before, as it waits until all files are full.  This causes data writes to be centralized into one disk/file and has a dramatic negative impact on performance when this happens – and causes a surge in tickets to Customer Support and impacts  SLAs.

My question is can I try turning auto-grow off on any data files on the T drive, and try again?  will it cause any issue or is there any other way.

The message is telling you why it failed, here is the root cause of the failure:

  Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.

Looks like the T volume runs out of space.

  I am not sure why it would need to write to this disk.

A hidden snapshot is taken and crash recovery run on the databases to bring it to a consistent state so that checkdb can run against the hidden snapshot copy of the database. This is how it works when checkdb is run online. There is the tablock option which is called 'offline' as it takes locks where the online way doesn't.

You are mostly correct. It doesn't force all writes to a single data file, however it is recalculating the proportional fill values which leads to hotspotting in a few number of files. This is known (though it seems no one cares) and that's why we had trace flags to grow the entire filegroup all together (TF 1118). This was changed and is now a database option in 2016.

Manually growing the data files each week seems excessive. If you know it's just going to grow by another TB each week, why grow it so low? If Performance Volume Maintenance Tasks is given to the service account or service sid you should be able to grow the data files fairly large with little impact.

  My question is can I try turning auto-grow off on any data files on the T drive, and try again?will it cause any issue or is there any other way.

Autogrow settings aren't your issue, sparse writes and running out of space on the volume, is.

Update:

  so what will be best way to deal these type of issues.shall I add more space?

Maybe. Depends.

If the datafiles are already on monolithic volumes, then I'd work on migrating them off the same volume. You can use mount points, storage spaces, etc., but having available space is what you need. Again, this has nothing to do with your autogrow settings and everything to do with the amount of free space available and the amount of space required to make the snapshots consistent.

What else could you do?

Well, you don't have to run CheckDB() all at once. You can break it up into smaller bite sized chunks of checks, things like using checkalloc, checkcatalog, checktable, etc. See the link here, I don't need to remake the wheel as Paul already has great articles.

  and in this case more ndf files are there then why it is writing in 23rd ndf files ,it should write to 28.ndf in X drive

The root cause of the issue is that you're unfamiliar with how CheckDB works, and that's okish. Since you know there is now a level of understanding needed, check the link I gave and read Paul's posts.

The snapshot files will be made for each data file, it's a snapshot and not actually writing to the database itself. When you read Paul's series I believe you will understand what and why - along with this answer.


Why SQL function always returning empty result set? Following function I have write which accept xml and returns table as result.

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN
    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT
        l.v.value('Value2[1]','INT'),
        l.v.value('Value1[1]','INT')
    FROM @XML_Details.nodes('/Listings/listing')l(v)
    RETURN
END

And following is the code I using to run against above function but it always returning Empty result.

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)

DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT *
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result

XQuery is case-sensitive, as are things generally when dealing with XML.  Correct your function to have an upper-case Listing element and it will work.  I note you also pass in @ID but don't use it and have accidentally swapped around your Value1 and Value2 columns. You should also always use the text() accessor when reading untyped XML for a performance boost, as in my example below.

This worked for me:

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN

    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT
        l.v.value('(Value1/text())[1]','INT'),
        l.v.value('(Value2/text())[1]','INT')
    FROM @XML_Details.nodes('/Listings/Listing')l(v)

    RETURN
END
GO

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)

DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT *
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result

My results:

Just as an aside, your function is simple enough to be an inline table-valued function rather than a multi-line one.  Inline TVFs generally perform better.  Post back if you want to know more about that.

HTH