Copy data through SSIS package from one machine to another I have data in few excel sheets which are to be loaded into tables. I've created a few SSIS packages to do this on my local machine.

Now I have to move these tables onto my client's machine, which is in another location. What's the simplest way to do this?

Depending on how much data you are loading into SQL Server from Excel, you can choose different routes :

When designing SSIS package, you can use configuration file. The advantage is that your packages will be customizable, like when you are developing it can point to your local server and when you want to promote it to prod, you just have to change connection string to point to PROD server. Good example can be found here.
Use Bulk Insert to load data.
As @listik mentioned, you can use Import/Export wizard as well.
You can use linked server too as described here.

SSIS will be more flexible and customizable.


Database Design Cars-Models / Car Classifieds I'm developing a database for Car Classifieds. I'm not sure how is the best way to design it. There are several features of cars like if it has 2 or 4 doors, if it is 4x4 or 4x2, engine capacity of 1.6 or 2.0.

All these features are particular for a car but they have to be limited to what that model has. So if someone is posting a bike, for example, it would not show the option in a drop-down for 4x4, or engines other than that model's.

I don't want to make this generic. It will only be for cars, so thinking about how it would be best for generic classifieds is not necessary.

What would be the best way to design this?

More Info:

My problem is how to design the relation between a Car Model and an actual car. For example, the Year of a Model could go from 1990-2000. When someone is going to add a car of that model I only want them to be able to choose from the options that model has. How do I store this in a database?

Perhaps something like this:

CREATE TABLE make (
    make_id INT NOT NULL,
    make_name VARCHAR(25),
    PRIMARY KEY(make_id);
);

CREATE TABLE model (
    model_id NOT NULL,
    make_id NOT NULL,
    model_name VARCHAR(25),
    PRIMARY KEY(model_id)
);

CREATE TABLE model_year (
    model_id INT NOT NULL,
    year_num INT NOT NULL,
    PRIMARY KEY (model_id, year_num)
);

CREATE TABLE transmission (
    transmission_id INT NOT NULL,
    transmission_descrip VARCHAR(15),
    PRIMARY KEY (transmission_id)
);

CREATE TABLE drive (
    drive_id INT NOT NULL,
    drive_descrip VARCHAR(10),
    PRIMARY KEY (drive_id)
);

INSERT INTO transmission (transmission_id, transmission_descrip)
VALUES (1, 'Auto'), (2, '4-speed manual'), (3, '5-speed manual');

INSERT INTO drive (drive_id, drive_descrip)
VALUES (1, '2WD Front'), (2, '2WD Rear'), (3, '4WD'), (4, 'AWD');

To allow someone to add a 2005 Toyota Camry, first you need to make sure Toyota is in the make table, Camry is in the model table, and 2005 is available for the Camry:

INSERT INTO make (make_id, make_name)
VALUES (1, 'Toyota');

INSERT INTO model (model_id, model_name)
VALUES (1, 1, 'Camry');

INSERT INTO model_year (model_id, year_num)
VALUES (1, 2005);

We keep certain data on car buyers at my job, and in selecting the car type (either currently owned or looking to buy), we have them select the year, then make, then model and options from drop-downs.  Each drop-down is populated on the fly based on previous answers.  First the year:

SELECT DISTINCT year_num FROM model_year;

Then the make (you can't pick Pontiac for 2013):

SELECT DISTINCT m.make_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable;

Then the model drop-down gets populated based on:

SELECT DISTINCT model_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable
    AND ma.make_id = @some_make_variable;

You get the idea. As far as constraining other options for a model, there are a few options, based on how strict you want to be, how much time you want to spend managing it, and how much you know about the auto industry.

Since all cars have a drive type and transmission, you might want to make those separate tables, like:

CREATE TABLE model_transmission (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- include this to restrict tranny based on some years
                           -- certain models had different trannies offered
    transmission_id INT NOT NULL,
    PRIMARY KEY (model_id, transmission_id) -- include year_num in PK if it is in the
                                            -- table
);

CREATE TABLE model_drive (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- again, if you are up to this level of control
    drive_id INT NOT NULL,
    PRIMARY KEY (model_id, drive_id) --include year_num in PK if it is in the table
);

Or you could use an option table and a model_option table, like this:

CREATE TABLE option (
    option_id INT NOT NULL,
    option_descrip VARCHAR(25),
    PRIMARY KEY (option_id)
);

INSERT INTO option (option_id, option_descrip)
VALUES (1, 'Transmission'), (2, 'Drive'), (3, 'Doors');

CREATE TABLE model_option (
    model_id INT NOT NULL,
    option_id INT NOT NULL,
    option_value VARCHAR(15),
    PRIMARY KEY (model_id, option_id) -- include year_num as above if desired
);

INSERT INTO model_option (model_id, option_id, option_value)
VALUES (1, 1, '1'), (1, 2, '1'), (1, 3, '4'); -- All Camrys now have automatic,
                                              -- front-wheel drive, and 4-door options
                                              -- available

I prefer the first approach as you can see the problem with referential integrity inherent in the second, plus it makes it more awkward to restrict available options.  The table for listed cars might be like this:

CREATE TABLE car (
    listing_id INT NOT NULL,
    year_num INT NOT NULL,
    model_id INT NOT NULL,
    transmission_id INT NOT NULL,
    drive_id INT NOT NULL,
    doors INT NOT NULL, -- might want varchar to include 'hatchback' or other options
    mileage INT,
    customer_id INT NOT NULL,
    PRIMARY KEY (listing_id)
);

If this doesn't address a particular feature you believe you need just add a comment.


Mysql Group replication vs Mysql Cluster [disclaimer] I'm not new to databases but totally novice about replication and cluster ...

As you may know Mysql made available a new functionality : MySql Group Replication.

Is anyone can explain me the fundamental difference between such architecture and a cluster ? any major functionality existing only one of both ?  node fault tolerance ? capability to come back to live status ?
Every advise is welcome ...

Thanks

Group Replication vs Galera (aka PXC)

Group Replication is only months old; Galera has been around for years.  Once GR establishes its worth (which I think it will), the answer may be something like this...

They are very similar.  Both do an excellent job of HA -- any single node crashing can be recovered from, usually automatically.  Both provide a high degree of read scaling.  GR promises to provide more write scaling.

Without knowing your requirements, and your tolerance for various things, I cannot advise on which way to go.  Perhaps 90% of MySQL/MariaDB/Percona users on this forum do not use any form of Replication.  So, the first question is, do you need any Replication solution?

The architectures are different, but the goals are similar.  That is, "there is more than one way to skin a cat".

Fault tolerance, in my opinion, is achievable only if you are willing to put nodes in at least 3 separate geographic locations (think flood/tornado/earthquake/etc).  Both allow for that.  I use the rule of surviving any single-point-of-failure, and include "datacenter" as a point-of-failure.

GR is a significant improvement over Oracle's previous offering, Fabric.

Since you did not mention Sharding, I did not mention it.

MySQL Cluster

The term "MySQL Cluster" is confusing; technically it refers to "NDB Cluster", which is significantly different than "Group Replication" aka "InnoDB Cluster".

NDB is discussed briefly by Ibrahim, but I would not go so far as to say "best solution ever".

NDB was originally a telco application for very reliable handling of telephone switching.  MySQL, Oracle, and SeveralNines have enhanced it _to be somewhat closer to a general RDBMS.  It retains its high reliability.

I would limit the use of NDB to applications that need the specific features that it provides.

Advice

For someone starting in the database world with MySQL, I would go with a non-replicated InnoDB-based dataset.  When replication / HA / scaling are needed, consider Group Replication or Galera.  Move to NDB only if it fits your application better.


Mysql Command line Parameter passing to sql file In windows mysql(5.7 version),
I want to run mysql commmand using command line for .sql file as input .
Also i was to pass some variable whose value should get used in .sql file.
somehow I am not able to achieve it.

mysql -u root -p xyz  -e "set @tmp='abc'; source d:/v_test1.sql"

Here I want to file v_test1.sql having mysql code and also variable @tmp being used in v_test1.sql.

But getting error:

  MySQL Error: Unknown column '@tmp' in 'field list'

I think issue might be because of version.
Please suggest some solution.

Try something like

echo set @tmp='abc'; > %temp%\temp.sql
copy /b %temp%\temp.sql+d:/v_test1.sql
mysql -u root -p xyz < %temp%\temp.sql
del %temp%\temp.sql


Cleanup after deleting a large table I have a database that once held a large table: ~6 billion rows, ~6TB of disk space, ~10 rows inserted/second. The rest of the tables, all told, comes to ~500MB and have infrequent writes. So the big table was completely dominating the database.

I've since deleted that table, moving some of its work to more compact tables, so the DB load isn't gone but the disk space pressure is.

Other than the DROP TABLE bigguy that I ran, is there anything I need to do to make Postgres feel happier? Are there any global indices that need to be cleaned out?

Postgres 9.5 running on Amazon RDS.

No other clean up should be required.  If you are really worried, you can watch your database size with:

select pg_database_size('your_database');

To make sure it shrinks by about the amount you expected.  It could take until the end of the next checkpoint to fully clean up.

If you were dropping tens of thousands of small tables is where you have you to worry about catalog bloat.  One large table is not an issue.


Capturing queries run by user on SQL Server using extended events Using Extended Events on SQL 2016 - how can we capture all queries run by users against a specific database. This helps to us understand what developers/reporting analyst/etl developers are running queries on production instance.

Expected Result:
UserName, TSQL Query Ran, Completion Time

You'll want to use the sqlserver.sql_statement_completed event, which includes the statement action field by default. Also, add the package0.collect_system_time action and there are several options for capturing the username.

This example uses sqlserver.session_nt_username to get current session NT user. It will output the results into a file and you will likely need to adjust some of the options as necessary for your environment.

CREATE EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.session_nt_username)
    )
ADD TARGET package0.event_file(SET filename=N'C:\Test\XE_QUERY_CAPTURE.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
    );
GO

ALTER EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER
STATE = START;


Azure SQL Server export via sqlpackage produces Error SQL71501 (Unresolved reference to object MS SyncAccount I have an Azure SQL Server database with a Geo-replication on Azure, which I would like to regularly backup via sqlpackage.exe (s. command below) using the replicated read-only database.
However, when executing it, it runs into an error (SQL71501) - the error occurs both on the primary as on the secondary database.
As this is a production database with thousands of users, is there any way to fix that in an "open heart surgery", i. e. without taking down the service, copying the database, fixing stuff then deploying that as the life database?
The command I run:
sqlpackage.exe" /a:Export /scs:"Data Source=tcp:xxx.database.windows.net;Authentication=Active Directory Password;Initial Catalog=xxx_Copy;UID=xxx;PWD=xxx"
               /tf:C:\Databases\xxx-$(((get-date).ToUniversalTime()).ToString("yyyy-MM-dd-T-HH_mm_ss")).bacpac

The result I get:
Validating schema

Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71501: Error validating element [dss]: Schema: [dss] has an unresolved reference to object [##MS_SyncAccount##].
Error SQL71501: Error validating element [TaskHosting]: Schema: [TaskHosting] has an unresolved reference to object [##MS_SyncAccount##].

This occurs when Azure SQL Data Sync was used on the database to sync with another database. The remnants from the DSS schema used by SQL Data Sync do not allow to export the database due to inconsistencies.
In order to successfully export the database, we need to:

Create a database copy. You can check how to create a copy of the
database using the portal at the portal. (there is also
documentation for doing this using T-SQL or PowerShel.

Remove the Data Sync metadata objects from the copy. You can find a
script to do it here ... Please make sure you are connected to
the copy.

Export the database from the copy.

Delete the database copy.

For more information, visit this article.


Where future rows don't exist I have the following table

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo)
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

The problem is this, I want to gather a list of errors but only if at a future time there is no resolution.

Ideally I'd get something like this back

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

Only because in the future there isn't some TaskId that has the exception present.

So I thought I could do this via group of sub queries something like as follows:

SELECT *
FROM TaskJournal
WHERE TaskId = 2
GROUP BY TaskId, TaskStatus
HAVING TaskStatus=3
ORDER BY TaskStart DESC

But the problem with this is that it returns both rows 4 and 2 (chronological descending order). But at some later point there appears to have been a resolution to TaskId=2 but not for TaskId=3.

Any ideas would be helpful.

MySQL-5.5

Bummer.

It appears Analytical Functions are not supported in v5.5

If it did, the solution would be something like:

select TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
from (
  select  TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
    ,row_number() over (partition by TaskJournalId,TaskId order by TaskStart DESC) rn
  from TaskJournal
)
where TaskStatus=3 and rn=1;

My Answer

Upgrade your database.


SQL Server 2000 : List of tables ordered from by number of referenced tables Is there any way in SQL Server 2000 to get a list of tables ordered by dependency? I need it for insert order in empty data base.

I can't turn off constraints, so I'm stuck with this solution.

Thanks

I think this function is a good starting point. The original source is lost in the sands of time, but I picked it up from Uri Dimant.

Normally I don't copy linked content wholesale, but this looks like it will benefit everyone by being here (I've edited a few typos).

  Some time ago I was asked to truncate lots of tables in client's
  database. As such, all those tables have FK and PK dependency. You get
  the error like that if you run TRUNCATE TABLE Categories in Northwind
  database as an example.

--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

  So , there is no such great problem as you know the database structure
  and can easily indentify parent-child tables. But what if you don't?
  I'd like to show the function (sadly, I don't remember by whom it is
  written) that returns the tables ordered by its level. Level 0 means
  that it is a referenced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

  Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2


I/O is frozen on database DBName. No user action is required We are on Windows Server 2019 on Azure VM, with SQL Server 2019 Enterprise CU4 installed. SQL Server differential backup jobs are failing intermittently.
I have looked into the SQL Server error log and I can see  the following error for all databases:

I/O is frozen on database DBName. No user action is required. However,
if I/O is not resumed promptly, you could cancel the backup.

followed by the following error logs:

I/O was resumed on database DBName. No user action is required.

After this error occurs, the differential backup job starts failing with the following error:

Executing the query "BACKUP DATABASE [DBName] TO  DISK =
N'X:\MSSQL..." failed with the following error: "Cannot perform a
differential backup for database "DBName", because a current database
backup does not exist. Perform a full database backup by reissuing
BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP
DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

At this point we cannot take any further differential backups until we have taken a full backup of the database.
I have checked and there is no other SQL Agent job running at that, and I cannot understand why this error is occurring.
I have done some research online and it appears that it was a known issues in SQL Server version 2016, 2017 and 2019 but it has been fixed in CUs for all those versions. The following article talks about this issue and confirms that issue has been fixed in SQL Server 2019 in CU2.
https://support.microsoft.com/en-us/help/4523102/fix-sql-server-database-remains-in-frozen-i-o-state-indefinitely-when
We have SQL Server 2019 with CU4 applied. So all the fixes should be in there from CU2. However, we are still experiencing the I/O frozen issue.
Any pointers or suggestions are much appreciated. Thanks in advance.

It sounds like you're using Azure Backups for SQL Server or Azure SQL Managed Backups. This is causing the I/O freeze and resume messages as it uses VSS and the SQL VSS Writer to perform the backups.
This type of backup causes issues with native SQL Differential backups (see here), with the solution being to either stop performing your native backups or stop the Azure backups.


One "Faster" server slower than the other I have 2 VMs:

VM1 - 8cores 32gb RAM
VM2 - 8cores 64gb RAM

I'm running identical process on both VMs at same time using SSIS Catalog, all processes are executed on it's own VM so no information travels through the network.. Aaaand I found out that load times for these processes are quite slower on VM2.

Load:

VM1- 15m
VM2- 45m

I have checked some performance metrics and did some basic tests... During the load I can see Network I/O Waits

VM1- 600ms/sec
VM2- 750ms/sec

I assume this network I/O wait comes from SSIS as SSIS is executing a query against SQL Server and then inserting the information in batchs, waits statistics tells me that both cases wait type is ASYNC_NETWORK_IO.

Also I did a test using SP_SPACEUSED to see the amount of rows inserted in both tables at same time and the results are not super accurated but VM1 almost doubles the amount of rows inserted...

Any further tests I'm missing? Any other checks that comes to your mind?

You are running in a virtualized environment so just because a server has more virtual RAM or more vCPU's doesn't mean it's "faster" (as you call it).

Several factors are abstracted by the virtualization layer and you would need to know and even benchmark them all.

For example, there is no guarantee the storage underneath the vmdk's is anywhere near comparable, so you would need to benchmark the storage and monitor it on a physical level.

The underlying physical CPU's could make a huge difference too. How fast are the physical processors? Are the CPU's overcommitted in the virtualization layer (i.e. are more vCPU's assigned per physical core) etc.
You could look at the CSTP or RDY values in VMWare among other things.

It really isn't possible to call one server faster than another because it has more virtual resources if you don't know the underlying hardware and virtualization configuration.

Some things you could check are

Storage performance
vCPU/pCPU ratio (overcommitting resources)
The vm's running on the same box (you might not know what your neighbour is doing unless you get access to the VMWare/Hyper-V counters such as esxtop CSTP and RDY values).
NUMA setup on the VM layer
power settings and hyperthreading configuration in the bios of the underlying machines to make sure they perform well when not fully stressed (like they are when running benchmarks)
...

I hope some of these pointers can help you troubleshoot further, but without any hard evidence about the underlying hardware there really isn't any point in comparing the virtual resource configuration to determine which VM should be "faster"

That being said, you may get some mileage looking at perfmon counters to determine disk latency or cpu load, or even use some VMWare counters such as effective speed in MHz or stolen time and you could get some information from wait stats but I still think getting access to some information about the VM configuration and underlying hardware is essential.

If you can, benchmark the underlying hardware.


Table structure for keeping track of multiple similar accounts I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different  years across the top, and the and ledger lines along the side. All of the tables are identical.

I want to put this into a database to make it more manageable, but I'm not sure if I have the best structure. Here are the basic table structures:

ledger_lines(ledgerCode, ledgerName)

account_X(accountCode, ledgerCode, year1, year2...)

With the bold bold as the primary key and ledgerCode referencing the ledger_lines table. Also, there would be 30 of the account_X tables eg. account_1, account_2... for all of the different accounts, all referencing the ledger_lines table.

Is this the best structure for managing this database? For example, and new field will have to be added to every table at the beginning of every year and to compare ledger lines across the accounts I will have to query all of the tables.

A separate table for each account will be difficult to work with.  For example, if you need aggregate values spanning all 30 accounts, you would need to look in all 30 tables.  And if you need aggregate values for only a subset, say 5, of those accounts you would need to look in just 5 tables but then you must specify the names of those 5.  It gets too darn fiddly fast.

You should consolidate those account tables into a single Accounts table which includes a field to identify which account each row is associated with. And when you want to see all the information for any one account, instead of an actual table, you can dynamically create a virtual table with an appropriate SELECT query.

SELECT [field expression list]
FROM Accounts
WHERE acct_num = 32;

Also think about happens when/if you need to modify the table structures.  You really don't want to have to make the same change to 30 tables.  BTW, I'm also uneasy about needing to add a new column each year.  Aim for a design which doesn't require that type of change.  Store information for different years by row rather than by column.  A rule of thumb is "columns are expensive; rows are cheap".  Try to work cheap!  :-)


SSIS push and pull performance After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS?

For example, I have 2 SQL Servers (A and B) and SSIS solution which extract data from A and load it to B (A->B). What's the difference in performance, between deploying SSIS package on server A or B?

SSIS is going to execute outside of SQL Server's address space. The data flow transformation within SSIS derives its performance through in-memory transformation to data. The more ram you can give it, the better theoretical performance you can obtain.

All things being equal between servers, I'd be surprised if there was an appreciable difference no reference sited

Things are rarely equal, even between servers that are spec'ed the same

If A has 32GB of RAM with SQL Server sized to take 24GB, that leaves 8 gigs for the OS and any other applications, like SSIS, to run in. If B is similarly allocated, things may still not be exactly the same - do people regularly RDP into one over the other? Do they do copy files around (#3 Check task manager) Lots of little things like that will skew the amount of memory left to the OS. Moral of the story, run it on the box that likely has the most amount of RAM available.

Unless you profile your SSIS packages and determine that you're CPU bound, in which case you need to see which server is less CPU intensive, which may vary based on the ETL schedule. I've had large servers that are basically idle during "working hours." When the ETL fires off, the CPU's go to 100 percent for few hours.

There is a destination within the Data Flow Task, the SQL Server destination. It only works if the package is executed on the destination server, B. When SSIS was brand new, everyone wanted to use this as it was screaming cheetah wheelies fast. Under the covers, they do this awesome memory space mapping thing into SQL Server - sort of like a partition switch. The data is in SSIS space and poof it's now in SQL Server's space. Made for really fast destination. Except it's fragile as hell. Not only does it have the aforementioned limitation (which you now understand due to the whole memory mapping exercise going cross server is not an option) but you would regularly encounter errors that basically said "oops, that didn't work, try again" Restart the package with all the same data and now it saves fine. No code changes, no data changes, nothing. Just randomly fails, or succeeds depending on how you view the situation. Moral of the story, use the OLE DB Destination - performance is just as good as SQL Server destination but without the baggage.

If A or B have different disk configurations and/or performance signatures, Spinning Rust on A while B has the better-than-SSD-memory-thing do your processing on the one with better resources, especially if you are pulling LOB types or see the "Buffers Spooled" performance counter being non-zero then your in memory ETL is now disk based file processing and you will feel the pain.


Column level security I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but aren't allowed to see items like salary or ssn. Is it possible to filter specific columns for users?

You can do this with a normal view, as long as the users involved haven't already got access to the base table.

EG:

SQL> create user reportuser identified by reportuser;

User created.

SQL> grant create session to reportuser;

Grant succeeded.

SQL> grant create synonym to reportuser;

Grant succeeded.

SQL> select user from dual;

USER
------------------------------
PHIL

SQL> create table basetable
(
  id number primary key,
  viewable varchar2(30),
  secret varchar2(30)
);

Table created.

SQL> insert into basetable values ( 1, 'hello world','this is secret' );

1 row created.

SQL> commit;

Commit complete.

SQL> create view reportview
as
select id, viewable
from basetable;

View created.

SQL> grant select on reportview to reportuser;

Grant succeeded.

SQL> conn reportuser/reportuser
Connected.
SQL> select * from phil.basetable;
select * from phil.basetable
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select secret from phil.basetable;
select secret from phil.basetable
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from phil.reportview;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

If you revoke permissions on the tables in question & create the views, along with a synonym for each users view that has the same name as the original table, it should be transparent.

EG:

SQL> select user from dual;

USER
------------------------------
REPORTUSER

SQL> create synonym basetable for phil.reportview;

Synonym created.

SQL> select * from basetable;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

You can also do this with Virtual Private Database, but I think it's an expensive extra licensed option. You use DBMS_RLS to configure the relevant security policies that you require.


Memory Optimized Azure Postgres I am trying to make sense of the "memory optimized" setting for Azure Database for PostgreSQL.  Main memory data management can be viewed as quite different from secondary storage data management (like column stores, and other main memory optimized approaches to store data), hence my confusion. Would Microsoft simply create a ram-drive or simple allocate more RAM for data? We still need to flush all the data from RAM to disk in case of a possible loss of electric power or hardware failure every now and then, how all this is accomplished on such instances?

I don't know much about this particular product - you probably should also ask this question directly to the vendor.
It looks like they'll simply give you a VM with more main memory and configure the database accordingly to use more memory for buffering of data file pages (shared_buffers). They might also configure more memory that can be used by each individual connection (work_mem), for example to sort tuples in memory instead of on disk, or for joins.
When asking the database to return data to you, having the pages already in the database's buffers hugely increase response times. Having more memory available for each query to do sorting and joins is advantageous with "analytical" queries.
Nobody in their right mind would think of selling PostgreSQL instances that use RAMdisks.
To achieve durability of changes to the database, PostgreSQL does not only apply the changes to the buffers (which will only eventually be written out to the disk), but it also records all changes into a Write-Ahead-Log (WAL), sometimes also called Transaction Log (XLOG).
Using the default configuration, a Writing Query is only confirmed to the client as having been written when this WAL has been flushed to the disk. I strongly assume that Azure follows this best practice.
In the end, aside from the amount of memory, and memory related settings, I doubt that there is much difference in the configuration of these machines. The database itself still works in much the same way, there is no general option to turn PostgreSQL into an in-memory database, it is always a database running on secondary storage, using primary storage to speed up most of the operations that one would notice judging by throughput or latency.


List Prepared Statements Anybody know how I can list the prepared statements that are in a SQL Server instance?

select
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.objtype = 'prepared'

That above query will return all of the prepared plans that are cached, as well as their corresponding SQL text.


PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.61 After asking last question about $id=$_GET['id']; and security issues I tried a PDO connection to my server. The following message appeared; ERROR: could not find driver.

I connected to phpMyAdmin - 2.11.1.2 and had this message appear;
Your PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.61. This may cause unpredictable behavior.

Is there a connection? Are PDO drivers required on the server? Would MySqli work?

Bob

There is a connection otherwise phpmyadmin wouldnt know what version the server is.

In most cases you can ignore that as only slight differences exist (for a full difference check the changelogs between the versions)

apt-get install php5-mysql

yum install php5-mysql

one of those should update your php library and not show that warning


Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem) I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install.  Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created.  It seems that MySQL 5.6 is much happier about accepting .ibd files without a corresponding .cfg file, as it will just assume the layout of the binary files matches the existing schema.  Which would be fine except datetimes are stored differently between the two versions:

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

I went ahead and altered a table with a datetime column in the middle of it to use datetime(6).  This will force the 8-byte datetime from the binary to fit nicely within the datetime(6) column, without overflowing into the columns to the right.

So, one problem solved: the datetime columns no longer corrupt all columns after them.  Hooray!

Next problem: now, the datetimes are incorrect.  I know this date should be within the last 10 years, but I am unsure how to correct it: 0000-00-09 04:59:58.8100193

I assume there is a math solution for this, where I can take the data from the column and convert it in place.  Something like execution_datetime = execution_datetime * math and come out with the correct datetime.

Has anyone tried anything like this before?  Is there a standard code snippet for this?

Any help would be much appreciated!

For posterity:

SELECT
     execution_datetime
    ,string_formatted_date
    ,STR_TO_DATE(string_formatted_date,'%Y%m%d%H%i%s')
FROM
    (
        SELECT
              day(execution_datetime) * POWER(2,41)
            + hour(execution_datetime) * POWER(2,36)
            + minute(execution_datetime) * POWER(2,30)
            + second(execution_datetime) * POWER(2,24)
            + (CASE WHEN DATE_FORMAT(execution_datetime,'%f') >= 8388608 THEN 1 ELSE 0 END) * POWER(2,24) /*extra second*/
            + DATE_FORMAT(execution_datetime,'%f') as string_formatted_date
        FROM execution_history
    ) a

It appears that there is some math error in the original import that misses a second when the 23 bit is 1.

Answer was arrived at by using the MySQL doc provided above to reverse engineer the bits and put them back in the old-style datetime format.  From there, STR_TO_DATE nicely parses the decimal representation into a datetime.  Extra second error was found through trial and error, while obsessively staring at the data and cursing.  I would not recommend this approach.


Is there a better way to add failed node back to postgres cluster? There are 2 nodes in the cluster: node1(primary) and node2(replica).
I will shutdown node1 and promote node2 as primary.
I'll add the node1 back to the cluster (as a replica this time), so I am using the following procedure on failed node1:

systemctl stop postgresql-12
rm -rf /var/lib/pgsql/12/data/*
pg_basebackup -h node2 -D /var/lib/pgsql/12/data/ -U replicator -P -v  -R -X stream -C -S pgstandby1
systemctl start postgresql-12

Is there a better way? I don't like that I'm deleting everything from /var/lib/pgsql/12/data/, and it takes some time until restoring, especially when there's a lot of data.
What are your considerations gentleman?

That's exactly what pg_rewind was written for. It allows you to undo any transactions that have happened on node1 after node2 was promoted. It can be seen as a fast version of pg_basebackup in this case.
There is no guarantee that pg_rewind will succeed. It depends on whether you have all the WAL since the last common checkpoint of node1 and node2. If there is not enough, you have to resort to pg_basebackup.
You can make sure that old WAL is kept around for a while by setting wal_keep_size (wal_keep_segments in older releases) appropriately.


Can PosgreSQL databases be restored from physical files? We had an EC2 instance running PostgreSQL. We created a new EBS volume for our more important DBs and stored them on the new volume by creating new tables spaces and setting their location. The system DBs were left in the default tablespace and location. Unfortunately the instance was accidentally terminated. We are trying to restore PostgreSQL with the databases.

The databases were on the EBS volume. We have spun up a new instance and have mounted the EBS volume and we can see the files. Is there any way we can get our files back?

As dezso said, its easy to change the data directory.

But I did a small PoC for this. So its possible to use existing physical files to another Postgresql.

Limitations

The new PostgreSQL should be the same version.
New PostgreSQL data directory and custom tablespace directory should be same.

My Postgresql config:

OS: Ubuntu 16.04
Postgresql 9.4

Install and config a sample database

apt-get install postgresql-9.4
mkdir /opt/pgdata
chown -R postgres:postgres /opt/pgdata

--in psql
create tablespace db LOCATION  '/opt/pgdata/';
create database bhuvi with tablespace db;
\c bhuvi
create table test (id int);
insert into test values (1),(2),(3),(4),(5);
bhuvi=# select * from test;
 id
----
  1
  2
  3
  4
  5
(5 rows)

Remove the PostgreSQL

--take a copy of Data direcory and table space directory
cp -R /var/lib/postgresql/9.4/main/ /opt
cp -R /opt/pgdata /opt/pgdata_bak

--clean up postgresql
apt-get remove --purge postgresql-9.4
apt-get autoclean
apt-get autoremove
rm -rf /etc/postgresql
rm -rf /var/lib/postgresql/
rm -rf /var/log/postgresql/
rm -rf /opt/pgdata

Fresh install and Replace old data and Tablespace directories

-- Reinstall
apt-get install postgresql-9.4
service postgresql stop
rm -rf /var/log/postgresql/9.4/main/
cp -R /opt/main /var/log/postgresql/9.4/
cp -R /opt/pgdata_bak /opt/pgdata
chown -R postgres:postgres /var/log/postgresql/9.4/main/
chown -R postgres:postgres /opt/pgdata
service postgresql start

Now check the new Postgresql

--in psql
\l+
   Name    |  Owner   | Encoding |
-----------+----------+----------+
 bhuvi     | postgres | UTF8     |
 postgres  | postgres | UTF8     |
 template0 | postgres | UTF8     |
           |          |          |
 template1 | postgres | UTF8     |
           |          |          |
bhuvi=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

bhuvi=# select * from test ;
 id
----
  1
  2
  3
  4
  5
(5 rows)


Get notified when user with administrative privileges logs in I have less than a handful of users that have sysadmin role membership.  I would like to be notified (emailed) when one of them logs in.  Is creating a trigger the best way or is there some kind of built-in function in SQL Server or maybe a SQL Server Agent-Alert that can be used?

I could build a job looking at:

SELECT name
FROM master.sys.server_principals
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 and name not like '%NT%'

or something of that nature but that would mean the job would have to run every second...

If you are looking to be NOTIFIED INSTANTLY AND CONSISTENTLY when a person with a specific permission logs in, Triggers are the best route.

If you want an audit of successful logins or failed logins, SQL Audit is your best route.

You can see a similar question here by Sergey on Stack Exchange.

There are other methods to achieve this goal, but it strictly depends on the requirements.

You can query for active sessions and join to system tables to find users active, you can audit who successfully logged in or not with the audit events and then correlate to the system tables too. (This becomes a retroactive or historical view, not a real time.) You can also use tracing / extended events to obtain this information and schedule a job to run off of. Here's an example of the Extended Events Logon Capture by Steve Jones on Voice of the DBA.

If you needed it instantly when the person logs in and consistently, triggers are your best resolution.

MSDN on Logon Triggers.

Here is an example of creating a LOGON trigger for all Sysadmin.

CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
DECLARE @UserInfo VARCHAR(255);
SET @UserInfo = (SELECT SYSTEM_USER);
IF @UserInfo = (   SELECT TOP 1 p.name AS [loginname]
                   FROM   sys.server_principals p
                   JOIN   sys.syslogins s
                       ON p.sid = s.sid
                   WHERE  p.type_desc IN ( 'SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP' )
                          -- Logins that are not process logins
                          AND p.name NOT LIKE '##%'
                          -- Logins that are sysadmins
                          AND s.sysadmin = 1
                          AND p.name = @UserInfo)
BEGIN
    PRINT 'Hello';
END;
GO

You may want to create a way to avoid hitting the trigger, please see this answer if you want to incorporate it.

Note: Be careful of logon triggers, you don't want to lock yourself out.


Good idea to have an index on datetime column? Assuming something like so:
select * from table where DatePart(YEAR, dateColumn) = 2012
Reasonable to have  an index on dateColumn?
Edit*
For SQLServer

Adding an index is definitely a good idea for this.  However, DATEPART(YEAR, datecolumn) = 2012 isnt' sargable so it will still do a scan of the index.
If you want it to use the index then you will need to do:
WHERE dateColumn >= '1/1/2012' AND dateColumn < '1/1/2013'

Please note the placement of the >= and the < signs to get the correct bounding box.


How to select 2 fields in where clause I want to match 2 pair of fields in where clause

select * from A where (x,y) in (select u,v from B)

Can some one guide me

You want to write this - which is valid SQL by the way - just not implemented in Sybase:

select *
from A
where (x,y) in (select u,v from B) ;

The easiest way is to rewrite is by using an EXISTS correlated subquery instead:

select *
from A
where exists
      (select 1 from B
       where A.x = B.u
         and A.y = B.v
      ) ;


Per create user % has a wildcard meaning. _ also matches a single character like the like expression.
* has no meaning and was probably put there by someone not familiar with what they are doing. Recommend dropping this use.


MYSQL count the number of members in a group Recently started working with MySQL and PHP so my setup is still a bit clunky most likely but here is how my 2 tables look right now:
Table 1 named clanlist

Table 2 named clanusers

What I need to do is check the number of users for each "clan" and show that number in the clanlist in the members column.
So what it should do is count the number of users who have "1" in their name and update the amount in table "clanlist". So if 3 users have "1" in the "clanname" column it should update the clanlist table and show 3 in the members column. I find it a bit hard to explain and I don't know if there are easier ways to do this so feel free to help me out with anything.
I would like to add that I already tried doing something similar in php code but it was unreliable and it required me to include the php code every time a user opened a webpage. Which obviously causes slower loadtimes for the page.
And one more thing, I saw something about the COUNT function which you can use in a query. But the code was really complex and I couldn't get it to work at all.
Created this in paint real quick:

The column members has to amount of rows in the clanusers table which have the clan_id from clanlist in it.

How about this?

UPDATE clanlist AS c SET members =
(
  SELECT COUNT(*) FROM clanusers AS u WHERE u.clanname = c.clanname
)


SQL Server 2016- send automatic email after store proceedure executes Is it possible to have a stored procedure send an email after it executes successfully. I have an alert account set up but dont know how to create a trigger after the procedure completes. very new here

There are a number of ways to do this.

Edit your SP to include a call to sp_send_dbmail upon successful completion of whatever logic you have.  I would suggest nesting this into some TRY/CATCH logic.  This will be the most direct route and will allow the notification(s) to occur regardless how the SP is called.
Call your SP via a SQL Agent Job, and configure it to send a notification to an operator upon conclusion of the job (e.g. completion, success, or failure).
Call your SP via a SQL Agent Job and include a secondary step calling sp_send_dbmail.  This is helpful if there are more than one party or email distribution list you want to notify as the built in notification methods within a SQL Agent job are rather limited.


SQL Server Primary Login Restrictions I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

  The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.


row locking within ACID transaction innodb I have a financial transaction that looks something like this (pseudo):

begin
    select record for update
    update record(same record as selected)
    insert another record into another table
commit

If I call SELECT ... FOR UPDATE within the transaction, will the lock be released for the specific rows only when the transaction is committed ?

According to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

the following commands can break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

As long as you do not any of these commands inside the transaction, the transaction should be stable. In fact, SELECT FOR UPDATE is so strong that row locks invade the indexes as well as the data. It could even set off possible InnoDB deadlocks. I once wrote about that in this post : select for update gives error on indexed column
As long as you keep transactions short and sweet, SELECT FOR UPDATE should not be a problem. If you want to protect your transaction from intermittent or unexpected deadlocks, MySQL 5.6 now has the following:

START TRANSACTION READ WRITE;
START TRANSACTION READ ONLY;

You should start the transaction with options like these. Why ?
According to that MySQL Documentation:

The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.

Your last comment was

so can i lock rows within a transaction another way other then for update

You can also run SELECT ... LOCK IN SHARE MODE. The Documentation says

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
For index records the search encounters, SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

Although possible, keep transactions short. Otherwise, you can increase the likelihood of deadlocks.
UPDATE 2014-02-13 21:57 EST
To clear up any misconceptions, please note this from the MySQL Documentation:

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

Therefore, if you execute SELECT ... LOCK IN SHARE MODE inside a transaction, the rows stay locked until the transaction commits or rolls back.


PostgreSQL: Pass table as argument in function I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example:

CREATE TYPE dataset AS(
    ChannelId INTEGER
   ,GranulityIdIn INTEGER
   ,GranulityId INTEGER
   ,TimeValue TIMESTAMP
   ,FloatValue FLOAT
   ,Status BIGINT
   ,QualityCodeId INTEGER
   ,DataArray FLOAT[]
   ,DataCount BIGINT
   ,Performance FLOAT
   ,StepCount INTEGER
   ,TableRegClass regclass
   ,Tags TEXT[]
   ,WeightedMean FLOAT
   ,MeanData FLOAT
   ,StdData FLOAT
   ,MinData FLOAT
   ,MaxData FLOAT
   ,MedianData FLOAT
   ,Percentiles FLOAT[]
);

I can create table using this template with:

CREATE TABLE test OF dataset;

I have seen many options in the API, but I am a little lost. I would like to know if it is possible to assign this type to function INPUT/OUTPUT parameters.

Let say that I have a FUNCTION called process that receives a sample of records from a dataset TABLE source, processes them and then returns a TABLE sink with the same TYPE.

That is I would like to know if it is possible to create a TYPE that behaves like this:

CREATE FUNCTION process(
    input dataset
) RETURNS dataset
AS ...

And that can be called like this:

SELECT
    *
FROM
    source, process(input := source) AS sink;

I wonder that it is possible with PostgreSQL, and ask how to do so. Does anyone of you know?

Here is a MWE of what I am trying to do:

DROP TABLE IF EXISTS source;
DROP FUNCTION IF EXISTS process(dataset);
DROP TYPE dataset;

CREATE TYPE dataset AS (
    id INTEGER
   ,t  TIMESTAMP
   ,x  FLOAT
);

CREATE TABLE source OF dataset;
ALTER TABLE source ADD PRIMARY KEY(Id);
INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0)
   ,(3, '2016-01-01 01:00:00', 12.0)
   ,(4, '2016-01-01 01:30:00',  9.0)
   ;

CREATE OR REPLACE FUNCTION process(
    _source dataset
)
RETURNS SETOF dataset
AS
$BODY$
SELECT * FROM source;
$BODY$
LANGUAGE SQL;

SELECT * FROM process(source);

But it does not succeed, it is like source is perceived as a column instead of a SETOF RECORDS with the type of dataset.

The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently.
But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:

Error when setting n_distinct using a plpgsql variable

Solution
You can still make it work using dynamic SQL with EXECUTE in a plpgsql function:
CREATE TYPE dataset AS (id integer, t timestamp, x float);

CREATE TABLE source OF dataset (PRIMARY KEY(Id));  -- add constraints in same command

INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0);

CREATE OR REPLACE FUNCTION process(_tbl regclass)
  RETURNS SETOF dataset AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process('source');  -- table name as string literal

See:

Table name as a PostgreSQL function parameter

Or search for related questions and answers on site.
To make it work for any given table:
CREATE OR REPLACE FUNCTION process2(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl);
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process2(NULL::source);  -- note the call syntax!!

Detailed explanation:

Refactor a PL/pgSQL function to return the output of various SELECT queries


I Need to Shrink My Database - I just freed a lot of space This question is asked in various forms here but the question boils down to:

I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.

How can I shrink my database? What files do I shrink?
What should my considerations be while doing this?
Should I do anything after?
What if it is a large database? Can I shrink it in smaller increments?

Some Initial Caveats:

It is generally known as a worst practice to ever shrink a production database or data file (Log files are another issue as this question talks about). I advise people to not shrink their databases in blog posts like this where I talk about "right-sizing" and good planning. I'm not alone there (Paul Randal, Brent Ozar, just to provide a couple more links). Shrinking a data file or database fragments indexes, is slow and laborious on your resources, can be a drain on your system and is just a bad thing to do, generally
In this case, we all know the risk is there, we are prepared to deal with it, but we freed a lot of space that we know we'll never ever need again. So in this specific type of case - shrinking makes a lot of sense as one of our options.

If you've read about the concerns and risks and you still need to do this shrink because you freed a significant amount of space, hopefully the rest of this answer will help you out. But do consider the risks.

There are two main approaches two consider here:

1.) Shrink Yes, do the actual shrink - Consider using DBCC SHRINKFILE instead of DBCC SHRINKDATABASE, you have more control over what gets shrunk and how. This will cause some performance degradation for sure - it is a large operation doing a lot of IO. You can potentially get away with repeated shrinks to a target size that gets progressively smaller.

This is the "A.)" example in the above DBCC SHRINKFILE link.. A datafile is being shrunk to 7MB target size in this example. This format is a good way to shrink repeatedly as your downtime window allows. I would do this in testing on development to see how the performance looks and how low/high you can go of an increment and to determine the expected timing in production. This is an online operation - you can run it with users in the system accessing the database being shrunk, but there will be performance degradation, almost guaranteed. So monitor and watch and see what you are doing to the server, pick a downtime window or period of lighter activity, ideally.

USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Always Remember: - every time you shrink you fragment your indexes and should do an index rebuild if you are going to shrink in chunks over a prolonged period of time. You are now incurring that cost each time if you can't get it done all in one window.

2.) New Database - You could create a new database and migrate data to it. You would have to script the empty database out and all of it's keys, indexes, objects, procs, functions, etc. and then migrate data to it. You could write scripts for this or you could use a tool like SQL Data Compare from Red Gate or other vendors with similar tools. This is more setup work on your side, more development and testing, and depending on your environment may also blow out your downtime window also but an option to consider.

When I am forced to shrink a Database
If this were my environment, I'd look to leave a fair/hefty amount of white space in the data file because I like being a disk hog and like to be prepared for future/unexpected growth. So I would be okay giving space back if we just deleted a majority of the space, but I'd never trust those saying "but it will never grow again" and still leave some white space. The route I'd probably go with (sigh) is the shrink approach if I had smaller downtime windows and didn't want to incur the complexity of creating an empty DB and migrating data to it. So I would shrink it a bunch of times incrementally (based on how many times I thought I needed to based on my testing in dev and the desired size. progressively choosing a smaller file size)and then rebuild the indexes.. And then I'd never tell any one that I shrank my database ;-)


How to assign day of the week to different tills Just wondering if someone would be able to please help me with a problem that I am trying to solve using T-SQL.
I have a table for till numbers assigned to 2 members of staff as seen below:
CREATE TABLE [dbo].[TillNumbersAssignedtoStaff](
    [AssignedWeek] [date] NULL,
    [StaffID] [int] NULL,
    [TillNumbers] [varchar](3) NOT NULL,

) ON [PRIMARY]
GO

INSERT INTO [dbo].[TillNumbersAssignedtoStaff]
VALUES ('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB5')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB4')
,('2020-10-19',190,'AB2')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB2')
,('2020-10-19',180,'BY5')
,('2020-10-19',180,'BA3')
,('2020-10-19',180,'BY4')
,('2020-10-19',180,'BA1')
,('2020-10-19',180,'BY2')
,('2020-10-19',180,'BE2')

I also have a daily allocation table as seen below:
CREATE TABLE [dbo].[DailyAlloc](
    [DayNo] [int] NOT NULL,
    [DayName] [nvarchar](20) NOT NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[DailyAlloc]
VALUES (1,'Monday')
,(2,'Tuesday')
,(3,'Wednesday')
,(4,'Thursday')
,(5,'Friday')
,(6,'Saturday')
,(7,'Sunday')

I would like to assign the daily numbers from Mon-Fri to the staff. Each daily count should not be more than the maximum average for the 5 days of the week e.g. If there are 9 tills to be assigned for the week, there should not be more than 2 tills assigned daily i.e. 9/5.
I would like the result to be as seen below:

Any help with this would be greatly appreciated.
Thanks


Get A Count From Table I want to see all fsid limit 25 from my table -- I tried this query

Select *
from btmt
WHERE Count(fsid) > 1
limit 24

But this throws an error of

  Aggregates are not allowed in WHERE clause.

What do I need to alter in order to run this query?

you can use something like...

SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1

...as a subquery (substitute name of your id field for "id") and wrap it up like this...

SELECT b.* FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt > 1
LIMIT 24;

Hard to tell exactly what you're looking for, but that will get you 24 unordered results from the btmt table with fsid value of greater than 1, although it might not be the prettiest way to do so, computationally speaking.

Please clarify if that's not what you're after--hope it helps if so.

EDIT: if you're looking for fsid values 1-25 it would be more like...

SELECT b.*, z.cnt FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt BETWEEN 1 AND 25
ORDER BY z.cnt; --through that in just for the fun of it :-)


Show backups at server level I have an SSRS report which shows the 'last backup time' next to each server in the list. I am getting this information by using:

SELECT TOP 1 @@SERVERNAME as serverName,
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
GROUP BY sdb.Name
ORDER BY LastBackUpTime DESC

The problem here is that if for example 7 out of 8 database backups fail in a job, that job is flagged up as 'failed', but the report will show that the last backup time was in that batch of failed backups, which it was, as it is selecting the one backup of 8 which succeeded.

Prior to this I was finding the last run time of the specific job from a table which I inserted the sysjobs and sysjobservers information into, as most of our backup jobs are hallengren jobs named identically. (see below)

SELECT serverName
         , MAX(CLastRunDateTime) AS CLastRunDateTime
    FROM  jobs
    WHERE name = 'DatabaseBackup - USER_DATABASES - FULL'

The problem with this is that there are some servers which have a non hallengren named job, so it involves 'fiddling' to insert the correct job name. There are also some servers which do not have agent and have backups taken manually, so job name cannot be used to pick up the last backup time.

How can I get a backup time at server level with as little 'fiddling' as possible. I understand the problems, as some hallengren jobs exclude certain databases, so it cant be based on 'when were all backups taken on this server' - it has to be on the one hand job specific, on the other hand not job specific.
Maybe something like, when the job name is 'X', 'Y', 'Z', then find it this way, when job name DOES NOT EXIST, then use max backup for last database? But that seems to involve the fiddling around that I don't want.

I'm not sure. Wondering if anyone has a good way of doing this.

The problem here is that if for example 7 out of 8 database backups
  fail in a job, that job is flagged up as 'failed', but the report will
  show that the last backup time was in that batch of failed backups,
  which it was, as it is selecting the one backup of 8 which succeeded.

To my understanding you want the job to report on the latest full backups on an instance when any of these are not meeting your thresholds.

In that case you could opt to get the the latest FULL backup of all databases, and then get the minimum value of that resultset.

;WITH CTE AS
(
SELECT
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT  @@SERVERNAME as serverName,
        MIN(LastBackUpTime) as LastBackUpTime
FROM CTE;

If you also need the database name of the database with the oldest successful full backup

;WITH CTE AS
(
SELECT
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT TOP(1)  @@SERVERNAME as serverName,
        LastBackUpTime as LastBackUpTime,
        DatabaseName
FROM CTE
ORDER BY LastBackUpTime ASC;

I would not use job execution stats, as they can return false positives.

Next to checking if the backup jobs did their job correctly, you should also validate your backups by restoring them somewhere.


Total of orders open on a given date for each date in a date range This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.

I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand seems to be the most promising in terms of speed.

What's different here, though, is I have to take into account two dates for a single item. So, given an OrderID item within a table of Orders, the OrderID has an Opened Date and a Closed Date. The Opened Date is always populated, but the Closed Date could be NULL.

OrderID  OpenedDate    ClosedDate
654554    12/1/2011     5/4/2012
678451    12/4/2011     3/2/2012
679565    12/8/2011     5/21/2012
701541    5/23/2012     NULL
...

I need to - efficiently - get back how many Orders had what we could term an "Open" status on any given date within a date range. The date range could span a couple of years.
(Yes, I do have a reference table of sequential dates.)

Date         CountOfOpenOrders
12/1/2011    175
12/2/2011    178
12/3/2011    195
12/4/2011    192
12/5/2011    191
...

If your priority is speed of selects, the following approach allows for extremely fast selects. Instead of storing a period, you want to store two events (period start and period end). Change column is 1 when the period begins, and is -1 when the period ends. If more than one event occurs on the same day, they must have different EventNumberPerDay. RunningTotal is the number of open peroids after the event has happened:

CREATE TABLE dbo.Events(
 PeriodId INT NOT NULL,
 Change SMALLINT NOT NULL,
 ChangeDate DATE NOT NULL,
 EventNumberPerDay INT NOT NULL,
 RunningTotal INT NOT NULL);
GO

INSERT dbo.Events
        ( PeriodId ,
          Change ,
          ChangeDate ,
          EventNumberPerDay,
          RunningTotal
        )
-- first period begins
VALUES  ( 1 , 1, '20120801', 1, 1),
-- second period begins on the same day
(2, 1, '20120801', 2, 2),
-- third period begins
(3,1,'20120803',1, 3),
-- second period ends on the same day
(2,-1,'20120803',2, 2),
-- fourth period begins
(4,1,'20120804',1,3),
-- fourth period ends
(4,-1,'20120805',1,2),
-- first period ends
(1, -1, '20120808',1,  1),
-- third period ends
(3, -1, '20120809',1,  0);
GO

Also you need a calendar table:

CREATE TABLE dbo.Calendar([Date] DATE NOT NULL);
GO

INSERT INTO dbo.Calendar([Date])
VALUES('20120801'),
('20120802'),
('20120803'),
('20120804'),
('20120805'),
('20120806'),
('20120807'),
('20120808'),
('20120809'),
('20120810'),
('20120811');

Once that is accomplished, then your select is very simple and very fast:

SELECT  [Date] ,
        coalesce(RunningTotal, 0) AS NumOpenIntervals
FROM    dbo.Calendar
        OUTER APPLY ( SELECT TOP ( 1 )
                                RunningTotal
                      FROM      dbo.Events
                      WHERE     ChangeDate <= [Date]
                      ORDER BY  ChangeDate DESC, EventNumberPerDay DESC
                    ) AS t
ORDER BY [Date]

Of course, this query is only correct if the data in Events table is valid. We can use constraints to ensure 100% data integrity. I can explain how if you are interested.

Another alternative is to just load your raw data, your periods, into a client application - your problem is absolutely trivial in C++/C#/Java.

Yet another approach is to use an RDBMS with fast cursors such as Oracle - that will allow you to just write a simple cursor and enjoy good performance, but still not always as good as my first solution.


Index not re-organised I am using Ola's INDEX Maintenance solution. Few indexes are not being re-organised after running job.Below is my code

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium =
   'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationLevel1 = 5,
   @FragmentationLevel2 = 30,
   @UpdateStatistics = 'ALL',
   @OnlyModifiedStatistics = 'Y',
   @LogToTable = 'Y',
   @PageCountLevel = 0

Few indexes which has fragmentation level 27 % still doesn't get re-organised even after running Index-optimize job.

I have went through frequently asked questions, it suggests about pagecountlevel which is already 0 in my case.

Also it has index_id =1 rather than 0.

My guess is that these indexes are very small.

Aaron Bertrand comments here

  Ola's solution is set to ignore indexes with less than 1000 pages (see
  the @PageCountLevel parameter). You can override that so that it cares
  about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

  I would stop worrying about small tables like this - let Ola's
  solution do its job, and worry about fragmentation when you can
  actually prove it is causing a tangible performance problem for a
  specific index. "Fragmentation is high" is not a problem on its own.

Additonally, Thomas Stringer comments here

  If an index is very small (I believe less than 8 pages) it will use
  mixed extents. Therefore, it'll appear as if there is still
  fragmentation remaining, as the housing extent will contain pages from
  multiple indexes.

  Because of this, and also the fact that in such a small index that
  fragmentation is typically negligable, you really should only be
  rebuilding indexes with a certain page threshold. It is best practices
  to rebuild fragmented indexes that are a minimum of 1000 pages.


Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question) I was given a compressed folder that contains number of subdirectories, and, among others, directories named .pgdir with number of numerically sequenced files in a <9999>.dat.gz format and one in a toc.dat format.

I cannot post the content of the files, but when I uncompress the <9999>.dat.gz, they each contain pretty structured, tab-separated tabular data with \N [sic] at the end line.
toc.dat has a mix of binary and strings, starting with PGDMP where I can recognize some postgres DDL statements, schema creation. etc.

To me, this looks like a postgres backup, but I am not familiar with this layout and how to restore it.

Is anyone familiar with these structures, and how could they be used to restore the database?

Use pg_restore to list its contents, dump it to SQL, or restore it to a database.


Unexpected index scan when doing query against varchar column with numeric parameter I have a test table with schema like this:

CREATE TABLE `indextest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

and here are the rows in the table:

mysql [localhost] {msandbox} (test) > select * from indextest;
+----+--------+
| id | name   |
+----+--------+
|  3 | 111222 |
|  1 | hello  |
|  2 | world  |
|  4 | wow    |
+----+--------+
4 rows in set (0.00 sec)

when I query the table against the name column with a string, it looks good:

mysql [localhost] {msandbox} (test) > explain select * from indextest where name='111222'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indextest
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 13
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

But if I use a numeric as the query parameter, explain shows that the query optimizer is doing index scan:

mysql [localhost] {msandbox} (test) > explain select * from indextest where name=111222\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indextest
         type: index
possible_keys: idx_name
          key: idx_name
      key_len: 13
          ref: NULL
         rows: 4
        Extra: Using where; Using index
1 row in set (0.00 sec)

and under some other conditions (found in online slow queries), the query optimizer even suggests doing similar queries with table scan.

I don't understand why it behaves like this but not just raises error or converts the numeric to a string automatically.

I'd assume it's because numbers can be represented multiple ways in text. According to the docs on How MySQL Uses Indexes:

  Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1' (sic). This rules out use of any indexes for the string column.

(I think 01.e1 is a typo which should read 0.1e1.)

Since a number does not equate to one and only one text value, the index can't be used.

On the other hand, this same situation does not apply in the opposite direction. A text representation of a number does equate to one and only one numeric value. In other words:

While numeric 1 could equate to texts '1', ' 1', '00001', or '0.1e1',
the texts '1', ' 1', '00001', and '0.1e1' all equate only to numeric 1.

So, if instead you had a numeric column and were comparing string values to it, the index can be used as you expect. (Really, I just tried it.)


How to display name on a column chart itself in SSRS, not on a legends How can I display legends name on a column chart itself in SSRS?

I believe you cannot do that. But, I came across something similar answered on the forum already. You can achieve a similar effect by following the steps in the link below.
Please go through the below link and check if it might be useful to you.
https://stackoverflow.com/a/9503789/2818351
Credits to the original author who answered the question.


Print Column Names for Table in Another Database I am in one database. How do I print out the columns names for table in another database?

create table dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(100),
     ZipCode varchar(9)
)

Answer within Current Database:

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM .sys.columns c
where c.object_id = object_id('Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

Print columns for table in Database B

Added Database B below,

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM .sys.columns c
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

You were almost there with your second answer - you just need to ensure you're selecting columns from the correct sys.columns table

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM DatabaseB.sys.columns c
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

The above works for me, adding the DB reference into sys.columns for DatabaseB.


SQL Server CTE Bottom to Top Recursive with Where clause I have an Employee Table with an EmployeeId, ManagerId and a Name field.

The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).

I found this link which helped to get the base of the code but I do not manage to make it work for my case

DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
INSERT @EmployeeTable VALUES (1,'Jerome', NULL )  -- tree is as follows:
INSERT @EmployeeTable VALUES (2,'Joe'   ,1)     --                      1-Jerome
INSERT @EmployeeTable VALUES (3,'Paul'  ,2)     --                     /        \
INSERT @EmployeeTable VALUES (4,'Jack'  ,3)     --              2-Joe               9-Bill
INSERT @EmployeeTable VALUES (5,'Daniel',3)     --            /       \                  \
INSERT @EmployeeTable VALUES (6,'David' ,2)     --     3-Paul          6-David       10-Sam
INSERT @EmployeeTable VALUES (7,'Ian'   ,6)     --    /      \            /    \
INSERT @EmployeeTable VALUES (8,'Helen' ,6)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @EmployeeTable VALUES (9,'Bill ' ,1)     --
INSERT @EmployeeTable VALUES (10,'Sam'  ,9)     --

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
    SELECT
        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
        FROM @EmployeeTable c
        LEFT OUTER JOIN @EmployeeTable  cc ON c.managerId=cc.EmployeeId
        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
    UNION ALL
        SELECT
            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
        FROM StaffTree t
            INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
        WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
)
SELECT * FROM StaffTree

In case you select the employee 3 hierarchy, the result should be:

EmployeeId | Name    | ManagerId
1          | Jerome  | NULL
2          | Joe     | 1
3          | Paul    | 2

Swapping the columns in the inner join in the recursive part is a way to go about this.

Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId

I deleted some parts that did not seem like they were needed.

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
    SELECT
        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
        FROM @EmployeeTable c
        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
    UNION ALL
        SELECT
            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
        FROM StaffTree t
        INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
)
SELECT EmployeeId,
       name,
       managerId
FROM StaffTree
ORDER BY managerId asc;

Result

EmployeeId  name    managerId
1           Jerome  NULL
2           Joe     1
3           Paul    2


What does a deterministic symmetric key mean? In sqlservercentral blog I've encountered this phrase

Symmetric keys are deterministic
when created, meaning that the same parameters run in different
databases will result in the same key. That means that the same key in
a different database (or instance) can decrypt data that was encrypted
in your production instance. Keep control of the parameters used to
create symmetric keys and secure them. That means watch out for
storage of these items in source control, in installation files,
upgrade scripts, etc.

I can't understand what it tries to say. Symmetric keys are protected by DMKs, so how can they be the same on different instances?
Thanks.

It's saying that it is possible to have different servers with the same symmetric key values generated. This may or may not be desirable per the business. For example, you can create your own symmetric keys that match.

The DMK just protects the symmetric key by encrypting it, however once decrypted the actual key portion could be the same. This can be desirable as symmetric keys have no backup/restore mechanism on their own.


What can we do in MySQL 5.0 Replication to address bandwidth concerns? I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of schemas, but I only need one per client so I supply the replication-do-db setting in my.ini to only replicate the schema that the client needs. The replication works, but when our clients get into regions of the world where internet access is only available via 3G wireless, which charge by data usage, they quickly exceed their data plan limits and run into expensive problems.

As I understand it, MySQL writes all transactions for all schemas into a single binlog file which means that each client has to download all of the transactions that are performed on every schema on the master, then once downloaded, apply the database filter per replication-do-db settings in the client's my.ini file.

To minimize this inefficiency I have employed the slave_compressed_protocol = 1 setting, which seems to reduce the transmitted data by 50%, but still causes our client's to quickly exceed their data limit rack up the 3G bill.

I can't imagine I'm the only one facing this, so I'm sure I'll get a ton of answers on how to achieve this by setting x = y. However, I can't find any documentation of such a setting, nor a recommended approach to take.

So far, here's my thought to a possible solution, please provide feedback or alternate routes:

Set up a "proxy" slave for each schema (on different box, or same box with a different MySQL instance/port)
Configure the proxy slave to replicate-do-db only the one database that the clients wish to replicate.
Configure the client's MySQL instance as slaves to the appropriate proxy slave.

This should result in the client only pulling the binlog data for their schema. The downside (as far as I can tell) is that it dramatically increases the complexity of our setup, likely making it more fragile.

Thoughts? Will this approach even work?

Note, we are running the MySQL 5.0 server on RedHat, but we could upgrade to 5.5 if it produces a solution.

SUGGESTION #1 : Use Distribution Masters

A Distribution Master is a mysql slave with log-bin enabled, log-slave-updates enabled and contains only tables with the BLACKHOLE Storage Engine. You can apply replicate-do-db to the Distribution Master and create binary logs at the Distribution Master that contains only the DB schema(s) you want binlogged. In this way you reduce the size of outgoing binlogs from the Distribution Master.

You can setup a Distribution Master as follows:

mysqldump your database(s) using --no-data option to generate a schema-only dump.
Load the schema-only dump to the Distribution Master.
Convert every table in the Distribution Master to the BLACKHOLE storage engine.
Setup replication to the Distribution Master from a master with real data.
Add replicate-do-db option(s) to /etc/my.cnf of the Distribution Master.

For steps 2 and 3 you could also edit the schema-only dump and replace ENGINE=MyISAM and ENGINE=InnoDB with ENGINE=BLACKHOLE and then load that edited schema-only dump into the Distribution Master.

In step 3 only, if you want to script the conversion of all MyISAM and InnoDB tables to BLACKHOLE in the Distribution Master, run the following query and output it to a text file:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=BLACKHOLE;') BlackholeConversion FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine <> 'BLACKHOLE'" > BlackholeMaker.sql

An added bonus to scripting the conversion of table to the BLACKHOLE storage engine is that MEMORY storage engine tables are converted as well. While MEMORY storage engine table do not take up disk space for data storage, it will take up memory. Converting MEMORY tables to BLACKHOLE will keep memory in the Distribution Master uncluttered.

As long as you do not send any DDL into the Distribution Master, you can transmit any DML (INSERT,UPDATE,DELETE) you so desire before letting clients replicate just the DB info they want.

I already wrote a post in another StackExchange site that discusses using a Distribution Master.

SUGGESTION #2 : Use Smaller Binary Logs and Relay Logs

If you set max_binlog_size to something ridiculously small, then binlogs can be collected and shipped out in smaller chunks. There is also a separate option to set the size of relay logs, max_relay_log_size. If max_relay_log_size = 0, it will default to whatever max_binlog_size is set to.

SUGGESTION #3 : Use Semisynchronous Replication (MySQL 5.5 only)

Setup your main database and multiple Distribution Masters as MySQL 5.5. Enable Semisynchronous Replication so that the main database can quickly ship binlogs to the Distribution Master. If ALL your slaves are Distribution Masters, you may not need Semisynchronous Replication or MySQL 5.5. If any of the slaves, other than Distribution Masters,  have real data for reporting, high availability, passive standby or backup purposes, then go with MySQL 5.5 in conjunction with Semisynchronous Replication.

SUGGESTION #4 : Use Statement-Based Binary Logging NOT Row-Based

If an SQL statement updates multiple rows in a table, Statement-Based Binary Logging (SBBL) stores only the SQL statement. The same SQL statement using Row-Based Binary Logging (RBBL) will actual record the row change for each row. This makes it obvious that transmitting SQL statements will save space on binary logs doing SBBL over RBBL.

Another problem is using RBBL in conjunction with replicate-do-db where table name has the database prepended. This cannot be good for a slave, especially for a Distribution Master. Therefore, make sure all DML does not have a a database and a period in front of any table names.


Modelling Hierarchical attributes I am trying to figure out the data model for a retailer.

The retailer has several stores across the country and they are modeled using the following hierarchy:

Channel -> Zone -> City -> Store

Each store contains several articles. And each article has attributes like

Activation flag (this indicates the presence of article)
Price
Supplier
Warehouse

Now, the retailer can set these attributes at any level in the hierarchy. Consider the following cases:

Setting price for an article at channel level will apply it to all stores.
The price set at a higher level can be overridden at any other level. For e.g., at a city level for just the stores in a city or for a particular store.
This applies to all attributes listed above.

As of now, they have modeled it using RDBMS by defining global rules at the top of hierarchy and calling out exceptions separately as individual rows. Say, price table, will have price set for an article at Channel level and any changes at any level will be specified separately. Obviously, this is not very efficient when fetching the attributes at the store level.

Sample Data

Assume Channel, Zone, City and Store are collectively called entities. Channel will have ids ranging >= 4000, Zone >= 3000, City >= 2000 and stores range from 1 to 1000.

A subset of the hierarchy relationship data is given below:

Channel   | Zone     | City    | Store |
----------+----------+------------------
4001      | 3001     | 2001    | 13    |
4001      | 3001     | 2001    | 14    |
4001      | 3001     | 2002    | 15    |
4001      | 3002     | 2003    | 16    |
4001      | 3003     | 2006    | 74    |

Price Table

ArticleID | EntityID | Price
----------+----------+----------
12345     | 4001     | 2.5
12345     | 2003     | 2.9
12345     | 74       | 3.0

Here, the price 2.5 for channel 4001 will be set for the article in all stores. The next two rows set the price exceptions in some stores. The second price 2.9 set for city 2003 will be applicable only for the article in store 16 as per the hierarchy relationship mentioned above. And the third row directly sets price 3.0 for the article in store 74.

Hope this gives an idea of current model. So, can you please suggest a better way to store this?

You could formalize the approach that you're already using and make it more efficient to both maintain and query.  Consider an ERD like so:

Note that all of your hierarchy is flattened into a single set of columns.  There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent.  I've also included left and right visitation numbers which would really help you with your price lookups.  Lastly there is a Level column to store the rank within the hierarchy.

If you're not familiar with visitation numbers please have a look at my answer to this question, where I go into quite a bit of detail.

So why is this better than what you're doing now?

You only track each item in the hierarchy once.  Instead of having
4001 show up in the channel column many, many times, you just have
one record with 4001.
The structure is flexible and allows for insertion of new levels at a later time.  It also allows for uneven trees, say for example ZONE 3001 is subdivided into three SUBZONEs and that cities are under the subzones there (but go direct to zone outside of 3001).
All of your prices are in exactly one place (which doesn't need any reading logic that goes outside of normal SQL queries.  Instead of having to have logic that joins a price to different Entity tables depending on what range the EntityID is in, you have one consistent price retrieval query.
You don't need coded meaning in your identifiers so you won't run into trouble when you get too many entities at any one level, thus breaking your coding scheme.
The price retrieval query is dead simple:

-- Look up the left and right numbers for the store in question:
declare @StoreLeft int
declare @StoreRight int
select @StoreLeft = H.left, @StoreRight = H.right
from HIERARCHY H where H.ID = @DesiredStoreId

-- Look up the lowest level (most granular) price for this store:
select TOP 1
  P.price_amt
, P.currency
from PRICE P
  inner join HIERARCHY H
    on H.Left <= @StoreLeft and H.right >= @StoreRight
  inner join ARTICLE A
    on P.article_id = A.id
where A.id = @TheArticleInQuestion
order by H.Level ASC   -- ASC or DESC depends on how you count levels.


Query showing a number instead of the column value I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the abbreviation itself instead of a numeric key. However, when I run the query, the StateAbbr column shows up as a numeric value instead of the actual value from the column.

I have tried joining on the StateAbbr table to pull the actual column from there, but I must be doing it wrong, because it causes the whole query results to go blank. I have also tried making sure the bound column is correct when I create a form off the table, but changing that value just made the column show up blank instead.

Any suggestions? Thank you.

A lookup table with a single value (StateAbbr) should contain two fields:  StateId (Primary Key) as an autonumber type, and StateAbbr as a text type.  Your Persons table should contain a number type field that will store the value held by the StateId field in the StateAbbr table.  So,

The query used would be something like:

SELECT Persons.StateAbbr, States.StateAbbr FROM Persons, States WHERE Persons.StateAbbr = StatesAbbr.StateId;

Paste this statement into the SQL view of your query builder, then switch back to the design view to see the graphical representation.

This will return the actual state abbreviation code that you have assigned in the StateAbbr table.

Edit: I have included a screenshot of a quick sample I threw together based on what you asked, showing the two tables (design view), the relationship between them, and the SQL query that delivers the results I believe you are seeking.  Modify these as you need to in order to make it work for you. This screenshot changes the query I provided above to better meet naming conventions for tables and queries.

NOTE:  In the Persons table, you will create the State field using the Lookup wizard.  Point it to the States table as its source, and only include the StateAbbr field.  The PK in the States table will not be shown at all.  When filling the data into the Persons table, you will notice that it is a drop-down box where you simply select the state you want.

Please let me know if you need me to clarify anything else.


Understanding Galera Status wsrep_local_recv_queue_avg Does wsrep_local_recv_queue_avg really represent the average size of the wsrep_local_recv_queue?

For the MariaDB version below I have problems to understand the status value of it, because the wsrep_local_recv_queue is 0 all the time, but wsrep_local_recv_queue_avg is sometimes very high (above 500), even though the node has been restarted 600 seconds ago and 580 of those 600 seconds the queue size is 0 and only 10-15 seconds after starting the mysql node the receive queue can be as high as 10000, but is being saved away fastly.

The wsrep_local_recv_queue_avg value is even also only decreasing in fractions when checking the value regularly.

So please enlighten me, what this value actually represents if not the average receive queue size for the node's uptime.

~ $ dpkg -l | grep mariadb
ii  libmariadbclient18                5.5.45+maria-1~wheezy         amd64        MariaDB database client library
ii  mariadb-client-5.5                5.5.45+maria-1~wheezy         amd64        MariaDB database client binaries
ii  mariadb-client-core-5.5           5.5.45+maria-1~wheezy         amd64        MariaDB database core client binaries
ii  mariadb-common                    5.5.45+maria-1~wheezy         all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii  mariadb-galera-server             5.5.45+maria-1~wheezy         all          MariaDB database server with Galera cluster (metapackage depending on the latest version)
ii  mariadb-galera-server-5.5         5.5.45+maria-1~wheezy         amd64        MariaDB database server with Galera cluster binaries

According to the MariaDB KB: Galera Cluster Status Variables:

  Description: Average length of the receive queue since the most recent
  status query. If this value is noticeably larger than zero, the node
  is likely to be overloaded, and cannot apply the writesets as quickly
  as they arrive, resulting in replication throttling.

So it not the average receive queue size for the node's uptime, but since the last time you queried this status.


Exec Stored Procedure on particular day of week I have a procedure which should execute on specific day i.e. only on Monday of the week.

How to call it from another procedure passing variable or parametre ?
without using SQL server agent

declare @wday as nvarchar
set @wday = (select DATENAME(dw,getdate()))

select @wday as day1

you should probably create an Agent Job

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-2017


Who Dropped MYSQL Table I am using Mysql 5.7 and 2-days back someone drop my table. I didn't enable my general log. How can i find the username who dropped the table?

By default MySQL doesnot log general SQL statements. Configure MySQL to do so. The MySQL documentation on logging (MySQL :: MySQL 5.1 Reference Manual :: 5.2 MySQL Server Logs) should help you do that.


how to change drive for Partitioned tables I have a large table should have been partitioned in different derives but all of them in one drive (D:\sql\import2006.ndf), (D:\sql\import2007.ndf).how can i move them in to their own drives like data2006,data2007 etc without loosing data.

You are using the terminology incorrectly and is not clear what you want to achieve. A table has one or more partitions. Each partition resides in a filegroup. A filegroup hase one or more files. A file is stored on a logical drive. A logical drive can be backed by one or more physical drives.

What is that you want to change?

Did you load data in the wrong partition of the table?
Did you load the data in an unpartitioned table and now you need it to be partitioned? (This is really the same question as the first one)
Did you placed the partition in the wrong filegroup?
Did you place one or more filegroup files on the wrong logical drive?
Did you place a logical drive on the wrong physical drive?

Each problem has a different solution.