texts: How to speed up query on table with millions of rows

texts:
The Issue:

I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are sending many measurements in different intervals on different times. I want to make a query which selects all the most actual ( 'actual' I mean the latest from all measurements made in each day) measurement of each day for 2 months e.g from 2013-01-01 to 2013-02-01.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns. I've also created auxiliary table that contains max(MeterDate) and MeasurementsId when the measurement was given. I've noticed that index can't be made on MeterDate because it contains date and time which is not useful for making an index on it. So i converted the MeterDate -> CONVERT(date,  MeterDate). I though that after joining The auxiliary table with [dbo].[Measurements] the query would be faster but still query takes more than 12s which is too long for me.

The structure of table:

Create table [dbo].[Measurements]

[Id] [int] IDENTITY(1,1) NOT NULL,
[ReadType_Id] [int] NOT NULL,
[Device_Id] [int] NULL,
[DeviceInterface] [tinyint] NULL,
[MeterDate] [datetime] NULL,
[MeasureValue] [decimal](18, 3) NULL

Every row of Measurements table include measurement value on direct MeterDate e.g. "2008-04-04 13:28:44.473"

Direct select structure:

DECLARE @startdate datetime= '2013-07-01';
DECLARE @enddate datetime = '2013-08-01';

SELECT *
FROM [dbo].[Measurements]
WHERE [MeterDate] BETWEEN @startdate and @enddate

Does anyone knows how to rebuilt table or add new or add indexes on which column that speed up query a bit ? Thanks in advance for any info.

Edit:

The table that I used was created by this query

with t1 as
(
    Select  [Device_Id], [DeviceInterface],  CONVERT(date,  MeterDate) as OnlyDate, Max(MeterDate) as MaxMeterDate
    FROM [dbo].[Measurements]
    GROUP BY [Device_Id], [DeviceInterface], CONVERT(date,  MeterDate)
)
Select t1.[Device_Id], t1.[DeviceInterface],t1.[OnlyDate], r.Id
INTO [dbo].[MaxDatesMeasurements]
FROM t1
JOIN [dbo].[Measurements] as r ON r.Device_Id = t1.Device_Id AND r.DeviceInterface = t1.DeviceInterface AND r.MeterDate = t1.MaxMeterDate

Then I wanted to join the new created table [dbo].[MaxDatesMeasurements] with old [dbo].[Measurements] and select direct rows

DECLARE @startdate datetime= '2013-07-01';
DECLARE @enddate datetime = '2013-08-01';

Select *
From [dbo].[MaxDatesMeasurements] as t1
Join [dbo].[Measurements] as t2 on t1.[Id] = t2.[Id]
WHERE t1.[OnlyDate] BETWEEN @startdate AND @enddate


texts: Can't select database for transaction log backup in maintenance plan

texts:
I am running into a problem while trying to save a transaction log database backup maintenance task:

Add the 'Back Up Database Task' to the maintenance plan.
Edit the task.
Select backup type: 'Transaction Log'.
Select any database running with Full Recovery Model.
Close the task.
Re-open the task. Notice that the database I selected is not checked anymore.

I can select all databases, all user databases, specific databases...none of them ever save. Every time I re-open the task that field is switched back to < Select one or more > option.

I've connected as user sa and I've connected with Windows Authentication. Same result. Also, I've double-checked that the databases I'm trying to backup are in Full recovery model.

I'm running SQL Server 2005 SP2 on Windows 2003 R2 SP2.

EDIT: This happens even if the database had a full backup run against it.


texts: Migrating or exporting a database from azure to SQL Server 2016

texts:
I am playing around with SQL Azure and in my learning curve. I Have migrated a database from my on premises instance to the Azure server. Now my question is, how do I move a database from Azure to on premises instance.

Source : Azure , Database name : Azuretest

Destination : Sql server 2016 on premises instance in my laptop.

Is Azure to On Premise database migration possible?


texts: How can I query on-premise SQL Server database from Azure SQL database using stored procedure?

texts:
I have a Azure SQL database and it has a stored procedure from where I am trying to join a table which resides in on-premise SQL server database.
Essentially, I am trying to query a table which sits in on-premise SQL server's database.
Are there any options to make cross-server queries from Azure SQL database?
Edit: I looked into the linked servers as mentioned in the comments but it seems linked servers do not work with Azure SQL Database Singleton and Elastic pools. They are enabled for SQL Server Database Engine and Azure SQL Managed Instance.


texts: Is it possible to validate DML statements?

texts:
Suppose that I want to delete a user named 'abc cba' from the database. So I run the following query:

DELETE FROM table WHERE fname = 'abc' AND last name = 'cba'; commit;

Now, there can be more users with this name I do not know about. The obvious choice would be to run select statement beforehand and check if I am deleting what I really wanted to delete, but I am still human and I can forget that and end up searching for backups to retrieve lost data.

My question is: is there a way to impose certain rules on statements after which they will not execute the DML statement (for example, do not execute the delete if the number of rows deleted exceeds certain threshold)?


texts: Size of the database and table while inserting and deleting in sql server 2005

texts: I have created a table : Table1. Inserted 100 thousand rows and deleted. I did the same around 100 times ( inserting and deleting). Will it cause of increasing the database and table size every time? ( I am using Delete instead of Truncate).


texts:
I have 4 Tables. i.e. crm_entry(id, prefix, firstname, lastname), crm_phone(id, crm_id, extension,value), sale_order(id, crm_id) and purchase_order(id, crm_id)

I want a table like this..

  | id | prefix | firstname | lastname | contact no | sales(count)
  |purchase (count)|

for getting data like this I tried inner join and left join to do so. but when Sales count is more than one.. the contact no gets duplicated in row as sales(count) times. same for purchase count. The query I used is written below:

select
crm_entry.id,
crm_entry.prefix,
crm_entry.firstname,
crm_entry.lastname,
group_concate(concat(crm_phone.extension,'-',crm_phone.value)),
count(sale_order.id),
count(pur_order.id)
from crm_entry
inner join crm_phone on crm_entry.id = crm_phone.crm_id
inner join sale_order on crm_entry.id = sale_order.crm_id
inner join pur_order on crm_entry.id = pur_order.crm_id
group by  crm_entry.id

I want contact number only one time (contact can be multiple but not duplicate).
How do I do?


texts: SQL Server order of execution and query plan

texts:
I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):

FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

However, I cannot understand how in the query plan the sort (which corresponds to the order by) is done before the select (given that they are read from right to left) if the order of execution is per above => SELECT before ORDER BY.

An simple example is as follows:

I excluded a JOIN on purpose as to avoid the case of having that sort corresponding to the JOIN.


texts: How to debug an hanging impdp importation

texts:
I am currently trying to import a database dump in Oracle XE 11gR2 On windows 7. I have the following command.

 impdp 'APPLICATION/APPLICATION@XE' dumpfile=APPLICATION.dmp REMAP_TABLESPACE=APPLICATION:USERS logfile=APPLICATIONIMPORT.log

I tried running the command in the windows command prompt but... nothing happens. The process does not exit or do anything. It print a copyright and just... hang without using any CPU or anything. The log file is not created, and SQL Developper show no import task when I query with SELECT * FROM DBA_DATAPUMP_JOBS;

I there anyway to see what is going on? Any log, table, place where an error could be outputted? That would really help me resolve this problem.

Note: If that can help, I am an administrator on my machine but part of a Windows Domain. It screwed up the Oracle XE install the first time but I got around by running the installer with a local admin account.


texts: Change MariaDB 10.0.17 (MySQL) master to a slave

texts:
I have a MySQL master/slave configuration

 --------        --------
| master |  ->  | slave1 |
 --------        --------

I want to kill my current master, transform slave1 to be the new master and have another slave pointing to slave1

 --------          ------------        --------
| master |        | new master |  ->  | slave2 |
 --------          ------------        --------
  KILLED

What is the best way to do this without database downtime?
I am using MariaDB 10.0.17


texts: I was wondering because recently I installed PostgreSQL, and I have previously installed DB Browser for SQLite on my Windows 10 computer. If it PostgreSQL has a DB Browser GUI for the tables, because I was using it and I was using SQL Query language to create a table, and insert values into the table. I thought this question would be on topic for the site, if its not please let me know.


texts: I need to use a permenent table instead of temporary but I still need the table's data to be private to the connection

texts:
I currently have an issue whereby an application which I maintain uses temporary tables. This is an issue as it's causing replication errors like found in this  mariadb issue.

I'm trying to think of a way that I can use permanent tables instead however I have the following dilemma.

The application in question is used concurrently by many users (not huge volume but enough for this to be a problem). An example temp table's data is populated with data specific to that user.

If I make the table permanent and truncate it at the beginning of every execution of the action in question then it has the potential to cause conflicts with other users on the system at the time.

Is there a way I can use transaction(s) and their isolation levels to be able to use a permanent table in the same manner as a temporary table?


texts:
I'm not really expert in backend. I have here some columns of notification table which has been used by a polymorphic relationship in Laravel.
+----+------------------+----------------+
| id | sendto_type      |   sendto_id    |
+----+------------------+----------------+
|  1 | App\User         |              1 |
|  2 | App\User         |              2 |
|  3 | App\Station      |              1 |
|  4 | App\Station      |              2 |
+----+------------------+----------------+

I want to query only from sendto_type column that has App\User attribute on it. Is it possible? and if yes, how would I do that.
These  are some columns from the users table which I referred on App\User
+----+------------------+----------------+
| id | firstname        |   lastname     |
+----+------------------+----------------+
|  1 | dummy            |    imdummy     |
|  2 | dummy2           |    imdummy2    |
|  3 | dummy3           |    imdummy4    |
|  4 | dummy4           |    imdummy3    |
+----+------------------+----------------+

I tried querying SELECT * FROM notifications WHERE sendto_type = "App\User". It was successful but returns no result. Please help


texts: Active/ Active SQL Server Clustering on AWS

texts:
I need to design and implement a highly available SQL Server 2012 clustering on Amazon AWS. In the white paper presented by Amazon, they provided an example of Active/ Passive SQL Cluster. Do you know whether Active/ Active is also possible?

Thanks


texts: Can error 1231 after importing a MySQL database be a compatibility issue?

texts:
ERROR 1231 (42000): Variable 'character_set_client' can't be set to
  the value of 'NULL' Query OK, 0 rows affected (0.00 sec)

  ERROR 1231 (42000): Variable 'collation_connection' can't be set to
  the value of 'NULL'

The above are the errors that I'm getting after importing my freshly dumped database from Bluehost (done through phpmyadmin, both with quick and with custom export methods).

Info Bluehost versions:
MySQL 5.5.42
PHP 5.4.24
Apache 2.2.31
phpMyAdmin 3.5.8.2

Local setup (no MAMP, XAMP, WAMP or LAMP) versions:
MySQL 5.5.25 (found through command line: mysql which version)
PHP 5.5.27
Apache 2.4.16
phpMyAdmin 4.5.1

Could it be related to incompatibility of versions?
Or do you think the origin of this problem lies elsewhere?
Also, once logged in to MySQL I cannot use mysqldump but if I'm not logged in, I can run this type of command:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Is that normal?

Anyways, I believe you can see how a newbee I am here so please bear with me.


texts:
I'm supposed to migrate a MySQL database into IBM DB2, but I thought I'd take baby steps first before I do something like that. So for now, I'd just like to add a database.

I'll admit that I have no experience at all with IBM DB2. Here's what I did so far:

I've installed the software, IBM Data Server Client Version 9.7 Fix Pack 7. Went without a hitch, although I must say, it appears simpler than either MySQL or MS SQL.
I've ran Control Center under General Administration Tools. I've had to run it as administrator for it to work.
I read somewhere that the flow is System -> Instance -> Database, so I created a system with the following details:

System Name: RNOLD-PC
Host Name: RNOLD-PC
Node Name: RNOLDNOD
OS: NT
Protocol: TCP/IP

Hitting the OK button with those details produces no error. For reference, the Computer Name of the laptop I'm using is RNOLD-PC.
I created an Instance under that with the following details:

Instance Name: testInstance
Instance Node Name: RNOLDNOD
OS: NT
Protocol: TCIP/IP
Host Name: RNOLD-PC
Service Name: RNOLD-PC
(Everything else is left blank)

Clicking the Discover button near Instance Name gets me an SQL4404N error: The DB2 Administration Server does not exist, while clicking the Retrieve Button near Port Number (which I left blank) gets me a "The Service Name RNOLD-PC was not found." If I try to add a database under that Instance, it gives me the same error.

Can I have some help on this? Where do I go from here, and how do I create a database?

EDIT: I added a Port Number (12345), which then allowed me to create a database, albeit with an error:

  [IBM][CLI Driver] SQL30081N  A communication error has been  detected.
  Communication protocol being used: "TCP/IP".   Communication API being
  used: "SOCKETS".

EDIT: Additional information:
 - Using Windows 7 32-bit


texts: SQL Query help: co-workers and employees from one query

texts:
I have two tables defining users and user employments. The user table looks like this:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fullName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
);

and the employments table:

CREATE TABLE `user_employment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employer_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `user` (`id`)
);

A user can employ users, but can also be employed. I need one query that can get all users that is a co-worker of a given user. I have tried with this query, but it only gives me employees for employer, or employer for employees:

    select u.id, u.username, u.fullName
      from user u
 left join user_employment e1 on e1.employee_id = u.id
 left join user_employment e2 on e2.employer_id = u.id
     where (e1.end >= NOW() or e1.end is NULL)
       and (e2.end >= NOW() or e2.end is NULL)
       and (e1.employee_id = <user id>
            or e1.employer_id = <user id>
            or e2.employee_id = <user id>
            or e2.employer_id = <user id>)
           and (e1.status = "confirmed"
                or e2.status = "confirmed")
               and u.id <> <user id>
  group by u.id;

If anything is unclear, please ask!

Thanks.


texts: SQL 2000: change collation of model database only- not master

texts:
One of our production SQL 2000 server has windows collation for master and msdb but SQl collation for model and tempdb.
We are trying to have the same setup on a test environment which at the moment has windows collation for all system databases
So, I got a copy of the model database with SQL collation from another server and did the following:

restored it as another user database (model2).
shut down the SQL service
moved the model database files (model.mdf and modellog.ldf) to another location
Renamed model2 database files as model.mdf and modellog.ldf so that SQL will think that this is the existing database.

It did change the collation but when I right click on model database and click on properties...it shows the following error:

  "Cannot resolve collation conflict for equal to operation"

Which it doesn't show in the existing production server.

Please help
Regards

Manjot


texts: SQL Server Named Instance is not listening on Port 1434

texts:
We just installed SQL Server 2016 on a Windows 2016 server.  I am having issues connecting to the Named Instance of that server. I have noticed that the server isn't listening on port 1434 either.  Normally we would see it in log as:

Server is listening on [XX.XX.XX.XX <ipv4> 1434>

But the only thing I see in the logs are:

Server is listening on [ 127.0.0.1 <ipv4> 63229].
Server is listening on [ ::1 <ipv6> 63229].

I can seem to connect to this named instance other than on the local server.  We have apps running that will hit this Named Instance but it seems like we are having issues connecting.

I also did a netstat -ao | findstr /I "processID" and I couldn't see the 1434 ports.  I had configured my TCP/IP to use the TCP Port 1434 for my IP but it's still not working.

Any ideas why it's not listening on 1434?

I checked in Configuration Manager and everything looked fine.  TCP/IP was enabled and SQL service restarted.  It doesn't seem to be listening on that port when it starts up.

The SQL Browser service is turned on. The TCP Port 1434 is assigned to the IP but the TCP Dynamic Ports and TCP Port is blank in the IPALL.

Maybe the question is how is the named instance supposed to be set up?  This was to replace an old server that was running SQL 2012 and that server had it set this way and it's been running fine.  So I copied the same setting and we can't seem to connect to the named instance.


texts: Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

texts: In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I want to have N standbys, and want to have synchronous replication enabled for some M subset of N, is that possible? A use case could be a cold backup. I don't care of data inconsistency for N-M subset, I just need to have that on M subset of N standbys.


texts: Partially contained databases and Windows Logins - best practices?

texts:
In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each database.

In 2012/2014+ my understanding is that this is not necessary, and that a user can be granted access to a database merely by granting their domain login permissions on the database directly.

Is there any reason why we as SQL Server Admins should define a server-level login for a Windows account if that account isn't intended to have any specific server-level role (backupoperator, sysadmin, etc)?  Is the best practice now to simply forego the server level login definition for Windows accounts altogether, unless you want to grant that login a server-level role?


texts: How to continuously keep two databases in sync

texts:
Me and a friend are developing a range of programs which uses a basic currency called "points" each user can gain and spend points for free.

The problem we face is MySQL we can connect to the databases and execute query's etc however I recently purchased a new Data Server however we would like to use more than just one server for our project incase one should go down for any reason (DDoS/DoS, Maintenance).

We have done many searches and we have encountered "MySQL Replication" (we are still reading about this) however we would like to know if there was a program or something which would keep our servers in sync (a bit like DropBox) where if a user spends 20 points on a server and the other server is down a program would have a record of the 20 points and update the other server when its back online.

I would just like some ideas if you need anything clearing up let me know, personally I would like to use just my database but he seems insistent that we use his server but sometimes his internet can really drop.


texts: MySQL quick database transfer between two servers

texts:
I am migrating a reasonably large (18GB) database used for a website from one server to another. There are two tables which make up 95% of that size and they do not have to be fully transferred for the site to function but eventually they'll have to be transferred. All tables are InnoDB.

I've examined the following options but I'm still unsure which would be most reliable and with least downtime.

Option A: mysqldump + import

Stop server A, mysqldump, transfer to server B and import. Definitely the safest method but downtime could be over 2 hours.

Option B: mysqldump w/out large tables + import later

Would be very quick but I'm not sure how the server would function importing the large tables while under load  and whether this would cause it to lock.

Option C: rsync datadir

I've seen this option mentioned in other question answers, and I wasn't able to make it work on a running server, understandably, but I can see it going wrong again even if server A is stopped fully before syncing. Also server A is on 5.6.25 while server B is on 5.6.28.

Option D: early import + incremental backup

I could make a --single-transaction dump on running server A, import it in advance and then make a further incremental backup whether through the binary log or other incremental backup systems.


texts: What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?

texts:
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful), and am looking for data sets that can defeat my optimization, as this same query will be used in a nearly identical database schema in a different database with different customer use cases.

Specify OPTION(Hash Join, MAXDOP = 8)

MAXDOP effectively tells the query optimizer to look for plans that can exploit parallelism
HASH JOIN is what tells the query optimizer to consider a Bitmap Filter

Non-nullable column filtered on EXISTS clause

When the column is non-nullable, the semi-join (EXISTS clause) guides the optimizer to choose a clustered index scan with a predicate that says something like PROBE(NonNullableColumn, 'IN-ROW')
Combined with the bitmap filter, the PROBE is highly efficient because it uses a bitmap filter.  This is, in effect, equivalent to a perfect Index Seek.  It's like a bookmark lookup on steroids.

However, in thinking through this cleverly constructed query I just made, I'm a little scared I don't know how to break it and when to expect it to stop working and the cost-based optimizer will choose a different plan.

Suppose I have 3000 unique values for NonNullableColumn, each with 30 million rows binned to those unique values.  In my performance tests so far, I passed in a set containing 15 as a unique test value.  This works great and I'm pretty confident this is the optimal plan.  What happens if I pass in a set containing random numbers - will SQL Server stop using the bitmap filter?  What's the "worst case" scenario here?

I've attached a picture of the query plan to re-inforce what I'm trying to test and how I can defeat (or anticipate) Murphy's Law.

In thinking through "what could go wrong", I found this in SQL Server Docs:

  The Repartition Streams operator (or exchange iterator) consumes multiple streams and produces multiple streams of records. The record contents and format are not changed. If the query optimizer uses a bitmap filter, the number of rows in the output stream is reduced.

and:

  SQL Server uses the Bitmap operator to implement bitmap filtering in parallel query plans. Bitmap filtering speeds up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves. The optimizer determines when a bitmap is selective enough to be useful and in which operators to apply the filter. Bitmap is a physical operator.

  Showplan Logical and Physical Operators Reference

According to Bitmap Filter (Star Join Query Optimization):

  Bitmap filter doesn’t work if:

  It’s not a hash join
  Out of date stats/missing indexes (cardinality/rows)
  Not enough memory
  Not enough threads (BF only works in parallel query – you need more than 1 CPU)

In my case, 1, 2 and 4 are not problems. 3 is evidently not a problem, at least in my example test. The memory requirement essentially means my StarJoinInfo table (Build table) must be smaller enough to fit in main memory; I wish I had a formula that gave me a upper bounds on how many unique values I could create a BitMap for.


texts: how do I connect to mysql database from visual studio without requiring SSL connection?

texts:
I have established a link between Mysql database and visual studio on my windows 10 machine.Unfortunately my application which is a desktop app can't open a connection to the DB. I keep getting this error whenever I try connecting to the database:
MySql.Data.MySqlClient.MySqlException: 'The host localhost does not support SSL connections.'

This is my connection:

***MySqlConnection conn = new MySqlConnection("user id=root;server=localhost;persistsecurityinfo=True;database=edutechsys");
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM TblLogin WHERE UserName = '" + TxtUserName.Text.Trim() + "' and Password = '" + TxtPassword.Text.Trim() + "'");
            conn.Open();
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter();
            **

How do I correct the error.


texts: Stored Procedure Not working as Expected

texts:
When I create the stored procedure without parameter it works as expected, but when I create it with parameter  it doesn't return any row.

DELIMITER //

CREATE PROCEDURE search()
BEGIN
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* GS Road.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* GS Road.*$'));

END//

The above procedure returns the rows.

But this doesn't return any value

DELIMITER //

CREATE PROCEDURE search(IN **qry** varchar(100))
BEGIN
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* ***qry**.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* ***qry**.*$'));

END//


texts: What column/s do I need an index for my queries?

texts:
I need to know where to put indexes in my queries. I am at lost as how to use it although I am searching about this for quite some time now.

Here are my queries: (in PHP)

1) In operator, I do use > and =. I use = often.

SELECT {columns} FROM {table}
      WHERE {field} {operator} {value}

2) This query is for pagination, limit is always equal to 10.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC LIMIT {start}, {limit}

3) This query is for checking the sent and received emails to and from other users.

SELECT {columns} FROM {table} alias1
  WHERE (alias1.{field} = {value} or alias1.{field2} = {value})
    AND EXISTS (SELECT max(alias2.date) alias_date FROM {table} alias2
       WHERE alias1.{field} = alias2.{field}
         and alias1.{field2} = alias2.{field2}
       GROUP BY {field}, {field2}
       HAVING alias1.date = alias_date)
       ORDER BY alias1.date DESC

4) A query using IN.

SELECT {columns} FROM {table}
  WHERE {field} IN ({in_data})
  ORDER BY FIELD ({field}, {in_data})

5) Query using ASC. Data in IN are numeric.

SELECT {columns} FROM {table}
  WHERE {field} IN ('in1', 'in2')
  ORDER BY date ASC

6) Query using AND.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
    AND {field2} {operator2} {value2}

7) Query with DESC.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC

In where column/s do I need to use index? The important column//s that in need of index? I need some help.

Note

The {field} and {field2} are only one field, e.g. WHERE {lastname} = {value}.

Update

I posted a more detailed query from my codes and how to implement index based on how I understand it. Here is the link: Are the index here will suffice to make the query faster?


texts: Best solution for PostgreSQL partial table replication

texts:
We have the following architecture of our system (PostgreSQL 9.6 is used on all servers):
1. there is a bunch of little servers ("sensors") storing data in some table
2. there is a big server ("management console") that collects data updates from all those servers and stores them in the table with the same name and structure
3. sensors can create new rows and update existing ones, the changes must be replicated to the console
4. console can update or delete rows that are relevant for particular sensors, these changes must be replicated to those servers
5. sensors must store only those rows that belong to them (there is a field "source" in the table storing the sensor's id)

My questions are:
1. This is a case of multimaster replication, right?
2. What replication technology would be most suitable for this use case, considering that we don't need autofailovers and sensors must store only a part of data, not the whole table that the console has?


texts: SSIS slow performance to insert short rows

texts:
(Edit: I changed the post after some more explicative tests)

I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of short length. The same server is much faster with longer rows.

Following are the resulting Elapsed vs Row length, testing with 1 million rows from flat file to table, with three configurations:

From Server1 to 7551: the configuration with bad issue;
From Server1 to 8221: server 8221 is another MSSQL Server, used as comparison;
From my laptop to 7551: I've used my laptop as source alternative;
From my laptop to 8221: final test is from laptop to comparison Db;

As you can see, the configurations #2 (red) and #4 (violet) has the same pattern, expected: longer the row, longer the elapsed.

Configurations #1 (blue) and #3 (green) maintain the same pattern only for longer rows, but show an offset for the shorter row length. Watching at the data, the offset is like 85-90 seconds for 64 bytes row and decrease to quite zero for 1170 bytes rows.

Note that #1 takes more to load 64 bytes rows than 2048 bytes rows (61.1MB in 91s vs 2GB in 54.8s).

The table is:

create table dbo.ALMG_TEST_LOAD (
  N bigint primary key,
  A nvarchar (4000) not null
)
Go

Finally, the ETL loads data with a Data Flow Task from a Flat File to an OLEDb destination:

Do you know why this could happen?

Note that:

Destination databases are similar: 7751 is backup/restored from 8221. Difference is that 7751 is SQL2014 and 8221 is SQL2012;
I'm the only one who is using 7751 and 8221 during tests. I've got same results in different days and hours;
SSIS version is 2012;
I've test with heap destination table or indexed destination table, and time profiles are the same;
ETL are performed with OLEDb connection and data loads are bulk.

Thanks

P.S.
I cannot access directly to destination server and it's not easy to talk to server admin to gather all kind of informations. Apparently they are not able to fix this issue and I have to help as much as I can. So any kind of suggestion is welcome.


texts:
So I am working on my DB design skills, and have tried to create a normalized MySQL database. Here is the diagram:

The reason I added the Address table is to break the address fields into separate columns, making the geography more searchable (i.e. search for zipcode).
Employer to Post will be a 1 (employer) to many (post(s))
My idea for position, since there will be a set number of positions would be to seed a table (Position) and store the position id in the Post table
Location works under the same premise of Address on the Employer table, however it may vary from the address on the employer table, earning it it's own table.
Finally, Industry will be seeded just like the Position table.

I would appreciate constructive criticism, as I am still working on becoming better at my schema design.


texts: SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601

texts:
I tried to backup the SQL Server 2008 database
using the following code:

BACKUP DATABASE [DataStore] TO  DISK ='E:\SQLServerDatabase\DataBasebackUpOct302012.bak'

and I get the following error:

  Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
  Cannot insert duplicate key row in object 'sys.syscommittab' with
  unique index 'si_xdes_id'.

  Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating
  abnormally. The statement has been terminated.

  Msg 3999, Level 17, State 1, Line 1 Failed to flush the commit table
  to disk in dbid 11 due to error 2601. Check the errorlog for more
  information.

Could somebody suggest a solution to this problem?

The version is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44  (64-bit) on Windows NT 6.1  (Build 7600: )

In reply to the comments below:

Aaron Bertrand,
Thank you. I am going to look into service packs and hope that is going to resolve the issue.

SQL-Learner,

Never used the change tracking services before.But we had unexpected Windows Server shutdowns due to mishandling 2-3 times.

Looks like I have to apply a service pack.

mrdenny,

Results after using DBCC CHECKDB
Errors:

Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
The statement has been terminated.
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information

Update 11/9/2012
I applied the Service Pack3 to SQL Server 2008 but I still could not create the back of the database. I have not rebooted the server as it didn't ask for it.
Will have to reboot and check if the patch applied.

Would appreciate any comments/ideas as to how to go about it.
Thanks.


texts:
I have a table where I put tons of unique numbers from 1 to 9999999
In order.

It has 3 fields.
idnum,username,t

I set t to 1 when I use an idnum so that no one else can have that for a member id number.

I want to put username as the only place we put the username because we made it where people can change their username. And that was never considered when I programmed the website, because the owners told me they would not allow it, but they had overwhelming requests for it so they allowed it.

The problem is that in 9 years worth of programming, the first 6 I used username in most.

I can go add idnum to those tables and update it. My question is, CAN I update the tables to get the username in those tables to pull the username data from that table above where the idnum matches?

Kind of like mapping it, like Microsoft's "Access" does.

Is that possible?
If so where do I find that documentation on how to do it?

My goal is to do that so that if someone changes their use name changing it in that table would update it in all of them.

best regards,
-Richard


texts:
We had 5 SQL Server 2012 servers configured with AlwaysOn.
A few days ago we dismissed 2 of them, we removed them from the availability group and then stopped SQL services. We need to reuse these servers for other purposes.

I need to know how to remove them also from the Windows Failover Cluster on which AlwaysOn is based.

Is it enough to click on the nodes and press "stop cluster service" and then "evict"?

Or there is something to do also with the SQL server installation?

Please note that the two servers will be formatted, so local SQL installation can be destroyed.


texts: SQL Server Distributed Availability Group Usage Pattern

texts:
I have got a client with DAG setup on 2016 & 2017 servers. I understand that their usage is most likely for DR purposes. What I'm a little unclear on (and I can't find any info on it) is how they are connected to (if at all). With a DAG / AG setup, does the application layer still only connect to the underlying AG (and not the DAG itself)? I've tried to connect to the DAG via SSMS and cannot. Is this by design? Does anything actually connect to the DAG itself or is it just in place to manage the synchronization across AGs? Any info most appreciated guys (and girls) :)

Regards,

Glenn


texts: SQL Server 2014, Volumes in Resource Governor

texts:
I'm looking at Resource Governor on 2014 and how it relates to volumes.  I can see where to set up MAX|MIN IOPS PER VOLUME on a resource pool.  That's not keyed by volume, however sys.dm_resource_governor_resource_pool_volumes is.  Should I read that as the pool's definition applies to every volume but the DMV reports what actually happened on each?

My understanding is this: say I have three volumes - E, F and G. I set min = 10, max = 300 on the pool.  Then there can be, at most, 300 IOPS against each drive for a max of 900 for the pool. The DMV shows what IO actually happened for each volume.  If the table/filegroup definition were such that the majority of the activity happened on one volume (let's say F) the numbers from the DMV would show large read_bytes_total and write_bytes_total for that volume and smaller numbers for the others.  Is this a correct understanding?

Neither BoL nor any of the blogs I've read make this explicit.


texts:
We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility mode.

My question is what do I need to be aware of if I have to turn it on to full compatibility mode in terms of potential conflicts/issues etc. Is the Upgrade Advisor tool still useful in my case as we already upgraded but running in backwards compatibility mode. Is there value in running that tool?

Any other information any one can share , will be useful.


texts: Detailed optimal column order with large text and bytea

texts:
I read this excellent answer on optimal column ordering and noticed the summation:

  Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't go wrong. Text or boolean do not have alignment restrictions like that, some other types do. In the end, you may save a couple of bytes per row at best. So, none of this is necessary for most people in most cases. But in your case it might save a couple of Gigabytes easily.

Does that mean that columns should be ordered from most space occupation to least?

If so, for bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths, do the same rules apply?  What about a text column that varies between 1kb to 5mb, heavily skewed to 1kb?

The bytea columns are the variables used in all read conditions.

The upper limit on this table's length is tens of billions of rows per year.


texts:
I have done some tests in flat file source trying to see if it read the file in order of the number of lines and all of them the number of the line read was inserted in SQL Server equally.

I was wondering if I can be sure that it will always read and insert in this order.


texts: Mongo is using 5-7% of CPU, so why is my computer so slow?

texts: I've installed MongoDB (not sharded, single location) on an AWS EC2 instance, and I'm finding that when I'm running large insert operations via pymongo, my entire EC2 instance gets very sluggish. Yet when I check CPU usage, it's reasonably low. Why is this and is there any way to contain it? Thanks for any advice.


texts:
SQL Server 2012 introduced a few new audit action groups.  Two that I'm interested in using are to audit log-ons/offs at the database level:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
DATABASE_LOGOUT_GROUP

The problem is, I also have SQL Server 2008 R2 instances that I'd like to deploy this on.  Are there alternatives to achieve this using native SQL Server Audit?  Or do I have to do this by other means (eg. SQL Profiler Trace, Logon/logoff triggers)?


texts: PostgreSQL row as jsonb "array", not "object"

texts:
We are trying to emulate a proprietary DB, which has "global primary keys", with PostgreSQL. That is what we would have liked to do (for many rows of many tables):

select 'some_table' as table_name, t.*
from some_table t
where id = 1

union all

select 'other_table' as table_name, t.*
from other_table t
where id = 2;

But, of course, that doesn't work, because every table schema is different.
Our DBA suggested this instead:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t
where id = 1

union all

select 'other_table' as table_name, jsonb_agg(t.*) as content
from other_table t
where id = 2;

Which would do it, but we're worried about the amount of data produced/transfered. Basically, every row of each table is returned as one  JSONB object contained in a single array. Now what bothers us, is the repetition of the column names for each row.

Could we instead somehow return a JSONB array per row, with only the values, based on the order of the columns in each table?


texts: PgAdmin: backup and restore schemas using the GUI

texts:
I know how to backup and restore one or more schema(s) from a PostgreSQL database using psql:

Go to the folder that contains the executables of PostgreSQL:

cd C:\Program Files\PostgreSQL\11\bin

Connect to the database:

psql -U user -p port -d database

Rename the schema to lowercases:

ALTER SCHEMA "schema_name"

RENAME TO "lowercase_schema_name";

Exit PostgreSQL:

\q

Save the schema(s) using the -n flag:

pg_dump -U user -p port -n schema1 -n schema2 database > "C:\path\to\directory\schema_backup.sql"

Add the schema(s) to the desired database (the existing schemas are preserved):

psql -U user -p port -d database -f"C:\path\to\directory\schema_backup.sql"

This procedure works well and is quick to perform. However, as I have to ask uninitiated people to do it, I would have liked not to have to go through the command prompt, but only through PgAdmin GUI.

It is very simple in PgAdmin to export and import an entire database by right-clicking on the database and using the backup and restore tools. However, I can't do the same thing just for one particular schema. In the same way as with psql (as described above), I want to save only one (ore more) schema(s) and then import it into a given database. When I save the schema in .sql format, create a new blank schema (for example in the same database) and restore the saved schema, the tables of the new schema are empty...

Any idea how to do this? Maybe I have to change some parameters in Dump options?


texts:
To be able to design a robust Job in SQL Server, we need to have log for each step of the job. I know there are different options available which help us for troubleshooting a job, but what if we want to record the number of affected records during the step, as well as being able to see the error if anything goes wrong. We have tables below in msdb database which we have query to see the job history:

sysjobstepslogs
sysjobhistory
sysjobactivity

Also the option available in each job step (where we have specify a text file to record the logs):

Output file

But What is the best practice in this regard? How can we log the number of records affected during each step?

Thanks in advance.


texts: Is it possible to write out images from SQL Server to a mapped drive?

texts:
I am using a cursor to write out images stored in a database to text.

Code snippet here:

DECLARE @SOURCEPATH VARBINARY(MAX),
        @DESTPATH VARCHAR(MAX),
        @ext varchar(MAX),
        @ObjectToken INT,
        @image_ID Varchar(255)

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
    SELECT <my data>
    FROM <my table>

OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DESTPATH = '<my path>'+ @image_ID

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID
END

CLOSE IMGPATH
DEALLOCATE IMGPATH

I am able to write to a local drive without a problem, but I cannot write to the network share that I'd like to write to. I've followed the steps here,

https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

in an effort to make the drive "visible" to SQL Server, but I still am not having any luck writing the images to this mapped drive.

Is this functionality supported, should I go down the route of checking permissions with our network guys, or is this not something that is possible?


texts: can you run sql server 2005 and sql server 2008 r2 in mirror mode?

texts: Must all db servers be running the exact same version to enable mirroring without a witness server? How about with a witness server in place?


texts:
What am I missing here?  If I run a print @sql statement and run that statement directly in SSMS I get the value that I am after.  However, if I run the dynamic sql and try to access Print @value1 outside the dynamic sql no value is returned.

How can I access the value of @value1 from outside my dynamic sql?  In my syntax below, it always returns nothing, as if the value does not exist!

Declare @Name varchar(100), @value1 varchar(100), @sql nvarchar(max)
Set @Name = 'Jalen'
set @sql = 'Declare @value1 varchar(100) Select @value1 = id
            from openquery(192.168.500.300,''Select id
             from massmailer where empname in ('''''+@Name+N''''')'')'
Print @value1
EXEC sp_executesql @SQL

I tried to modify my Exec statement to:

EXEC sp_executesql @SQL, @retvalOUT=@value1 OUTPUT;

This gives me an error of:

  Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
  Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.


texts: INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56

texts:
mysql> SELECT * FROM GLOBAL_VARIABLES;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

mysql> show global variables;
ERROR 1146 (42S02): Table 'performance_schema.global_variables' doesn't exist

I googled that and found links to tocker.ca which seems down.

$ curl  http://www.tocker.ca/2015/08/18/a-followup-on-show_compatibility_56.html
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

<title>404 Not Found</title>

Not Found

The requested URL /2015/08/18/a-followup-on-show_compatibility_56.html was not found on this server.

Where do I see what settings/variables have changed from default one (in my setup)?

Any background about this change?


texts: Is it safe to pg_dump and pg_restore a new postgres database that has malware?

texts:
I'm pretty sure there is a crypto bot eating up my CPU through a postgres script. I would like to create an entirely new VM, and move my database with it using pg_dump and pg_restore. I already checked my postgres for new users, tables, databases; couldn't find anything odd there which could comprise me if I move my data. I'm a little worried however because the bot is some how getting access to my postgres, and nothing else on my VM.
Thank you for the help.
EDIT:
I managed to move my db with pg-dump and pg-dumpall. It's been a few days since I destroyed my last VM and made a new db with better security. It looks like I'm in the clear at least for this same attack.


texts: Load Balancing multiple databases in a multi-tenant setup

texts:
We have a multi-tenant setup where each client of ours runs a DB that is identical to every other client. We were using Mysql sofar and with CentOS 7.x ,switched to MariaDB.

If we want to distribute the load so that requests for Databases 1-500 go to Node-A, request for Databases 501-1000 go to Node-B, would that be possible through some out of the box solution like haproxy? I looked into their documentation but the "balance" options seem to be about distributing the load based on pre-defined headers/values rather than my use-case above. Would it be possible to add a custom "balance" algorithm that will send the requests to the corresponding nodes?

The other approach I was considering was to use a local key-value store that contains the information about which clients are in which Node and switching to those based on the request. I also want to have master-slave replication for these, but have already handled that through a proxy in my application.

Our application runs on PHP(Symfony Framework) with Mysql, if that matters.

How would you design a system that will have few thousand databases and be able to spread the load?


texts:
I have a corrupt blob in a row.
I'd like to replace that with another blob from the same table.

How can I do that with sql statements only? (No file system access if at all possible.)


texts: compare results between two select queries MySQL

texts:
I'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query.
"Correct answer" query is in database, and the comparable select is inputed by user.
I read about EXCEPT and INTERSECT but they don't exist in MySQL.
For example I have this database structure:

Question from the application:

List the first name, last name and date of birth of all female students whose first name is Yana but their last name is not Taylor.

correct answer-
SELECT FIRSTNAME, LASTNAME, DATEOFBIRTH
FROM PERSON
JOIN STUDENTS ON PERSONID=STUDENTID
WHERE GENDER=’F’ AND FIRSTNAME=’YANA’ AND LASTNAME<>’TAYLOR’

I want to check that the returned result is identical. wrong answer will be MySQLquery for example with aliased columns:
SELECT FIRSTNAME as first_name, LASTNAME as last_name, DATEOFBIRTH as date_of_birth ....

or even like statement (if it returns more results):
WHERE GENDER=’F’ AND FIRSTNAME LIKE '%YANA%' AND LASTNAME<>'TAYLOR'

My question is how to compare results between two queries that:

Their returned row count is the same
Their column names are equal
It's from the same table
And maybe return some indication for true or false that they are identical?


texts: Restore mongodb database from blockstore snapshot backup

texts:
I have a situation where I need to restore the mongodb database from the blockstore snapshot backup taken from the ops manager which is used to manage 3 node replica set. I need to restore this backup into the new completly  standalone instance in the DR site.
I am new working with MongoDB and not sure where to start.
I tried to do some research but could not find an exact solution.
Any suggestions please ?


texts: Search SQL Server table for a hexadecimal range

texts:
I have a table of hexadecimal values stored in a varchar(32) column. The values can be anywhere between 000FFF100000 and 000FFFA90FFF

I would like to find all rows between 000FFF300000 and 000FFF7FFF05.

Any suggestions?


texts: mongo user can not connect from bash on localhost

texts:
In mongo shell, I ran the following commands:
use mydatabase
db.createUser( { user: "john", pwd: "mongopass", roles: [ "readWrite", "dbAdmin" ] });

Mongo reported a success response. So then from terminal, I typed mongo --username john --password, and when prompted for password, I typed mongopass.   But the result I got was:
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Error: Authentication failed. :
connect@src/mongo/shell/mongo.js:374:17
@(connect):2:6
exception: connect failed
exiting with code 1

How do I login to mongodb with a username and password?

This is my /etc/mongo.conf
# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true
#  engine:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile
  timeZoneInfo: /usr/share/zoneinfo

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

#security:

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:


texts:
I have an issue with SQL Developer - it is not possible to set dbms_output buffer larger than 1 000 000 symbols, while it works in SQL*Plus.
This script:

SET SERVEROUTPUT ON SIZE UNLIMITED

    BEGIN
       for c in (SELECT rpad('a',1000,'a') ll from dual connect by level <=2001) loop
         dbms_output.put_line (c.ll);
       end loop;
       dbms_output.put_line ('www');
    END;
    /

works in SQL*Plus, but fails with:

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 3

in SQL Developer.

I've tried to use dbms_output view in SQL Developer and set size to unlimited as described here, but the same amount of symbols is printed and there is no 'www' line. Version of SQL Developer is 4.03.16. Is it a known issue?


texts: Count the number of days in each month between two specified dates

texts: I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:


texts:
I've a table called BOND which have columns BOND NAME, COUNTRY of ISSUE and ISSUE DATE

BOND  Country  ISSUE_DATE
===========================
B1    INDIA    20-AUG-2017
B2    ISRAEL   18-AUG-2017

How do I find the issue date is weekend or not based on the issue country of BOND, without changing NLS_TERRITORY

SELECT TO_CHAR(ISSUE_DATE,'DY') DAY FROM BOND;

In the above dates 18-AUG-2017 is a weekend in ISRAEL but not in INDIA


texts: SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex

texts:
I observed blocking and time outs in one of our production server and there is prod outage to one of the application continuously,

When i run sp_BlitzIndex i got Aggressive Over-Indexing, ran script in more info script it didn't find any missing indexes,

  Aggressive Over-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits

The Create Table script contains below ALTER TABLE script..

  ALTER TABLE [schema].[table] ADD CONSTRAINT [Primary_Key_Index] PRIMARY KEY ( [ID] ) WITH (FILLFACTOR=90, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

In this table Primary Key is non clustered Index and Non_Clustered_Index is primary key

Can anyone suggest how do i clear this over indexing?

Thanks a ton in Advance


texts: What are the best practices regarding lookup tables in relational databases?

texts:
Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column.
For example, suppose we have a lookup table called party (meant to store information about political parties) that has two columns:

party_code_idn, which holds system-generated numeric values, and (lacking business domain meaning) works as a surrogate for the real key.
party_code, is the real or “natural” key of the table because it maintains values that have business domain connotations.

And let us say that such table retains the data that follows:
 +----------------+------------+
 | party_code_idn | party_code |
 +----------------+------------+
 |              1 | Republican |
 |              2 | Democratic |
 +----------------+------------+

The party_code column, which keeps the values 'Republican' and 'Democratic', being the real key of the table, is set up with a UNIQUE constraint, but I optionally added the party_code_idn and defined it as the PK of the table (though, logically speaking, party_code may work as the PRIMARY KEY [PK]).
Question
What are the best practices for pointing to lookup values from transaction tables? Should I establish FOREIGN KEY (FK) references either (a) directly to the natural and meaningful value or (b) to surrogate values?
Option (a), for example,
 +---------------+------------+---------+
 | candidate_idn | party_code |  city   |
 +---------------+------------+---------+
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |
 +---------------+------------+---------+

has the following properties1:

Readable for the end user (+)
Easy to import-export across systems (+)
Difficult to change the value as it needs modification in all referring tables (-)
Adding new value is not costly (=)

I think it is almost like “pass by value”, to draw an analogy from function call in application programming jargon.
Option (b), for instance,
 +---------------+----------------+---------+
 | candidate_idn | party_code_idn |  city   |
 +---------------+----------------+---------+
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |
 +---------------+----------------+---------+

has the properties below:

Not readable for the end user (-)
Difficult to import-export as we need to de-reference it (-)
Easy to change values, as we are only storing references in transaction tables (+)
Adding new value is not costly (=)

It is very similar to “pass by reference”, if comparing to function call in app programming parlance.
Import-Export can also be done in a different way, i.e., just by populating the look-up table again and then re-seed the surrogate column. I hope I am getting this right, this is something I have just heard as a possibility.
1. Note that +, - and = indicate the benefit of those properties.
Question
Quite importantly: Is there a difference between a lookup (or code) table and a FK reference if we are just going to use the latter approach? I think they work just the same.
Related resources

How Important Are Lookup Tables?

Best Practices With Code Or Lookup Tables

Lookup Tables Best Practices DB Tables Or Enumerations


texts: pg_basebackup could not get WAL end position from server

texts:
I'm  trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's displaying error:

could not get WAL end position from server

I've found few Q&A about this issue but all resolution was to check owners of files. In my case all files owner is user postgres, chown also OK

What could be the trouble

Server Ubuntu 13.10 Postgresql 9.1, database is having 2 tablespaces.

EDIT

Exact version is: PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.8.1-10ubuntu8) 4.8.1, 64-bit

Exact command is:

pg_basebackup -D /home/osm/bkp_binary/ -Ft -P -v -x -z


texts: innodb_buffer_pool_size more data than pool size

texts:
I have 64gb ram on server.
InnoDB buffer pool is set to 26.1gb

[OK] InnoDB buffer pool / data size: 26.1G/24.3G

Im wondering what happens if data over grow current pool size, as i understand it should store more important data and data more often used in pool and other access from hard drive ?

I had similar situation before, and as soon it has overgrown pool size mariadb server become unstable, i have been trying to lift up innodb_buffer_pool_size to 32gb but it caused stability problem as well. Even that most of time memory usage is 33gb/64gb and most of this ram is used by mariadb server, only 500mb ram is used by other applications. There other half seems not used. I have temporary solved it by truncating unnecessary data but this time i can't do it.

I have considered to use swap but since i have only 2x2TB Soft Raid Mode 1 and if InnoDb will read from disk anyways there is no point to do so i guess.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

Shows that i should use 60gb innodb pool size.

If it's important innodb_flush_method=O_DIRECT


texts: How do I set up privileges for different schemas?

texts:
How do I set up 2 schemas with a total of 5 users?
Schemas:

hp
hpc

Users:

dba - has full access to both schemas (hp, hpc)
hp_dbo - owns all objects in hp schema
hpc_fbo - owns all objects in hpc schema
hp_application_user - has select, create, drop, alter, execute privilege in hp schema
hpc_application_user - has select, create, drop, alter, execute privilege in hp schema

How do I realize this privilege structure? I was playing around with MySQL-Workbench but I ended up setting wrong permissions and losing access to my database for my application...


texts:
Is there a Excel version for this article? I have been searching for this past days but there is no luck.

https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/


texts: Selecting ports for Availability Groups Listener and SQL Instances

texts:
Experts,

I have 2 node Availability Groups with  File share witness.

We chose the non standard port 17333 for the Listener and 15333 for the 2 SQL Server instances.

One of our newer application was giving issues connecting to listener and was expecting same port number for listener and sql instance.

Can someone advise what is the best practice.

Possibly we can have have
a) Same ports for listener and all sql instances
b) Different port for listener and same port for sql instances
c) Different port for each of listener and sql instances


texts: Why I can tnsping Server P from Server A but cannot tnsping Server P from Server B?

texts:
Here is my situation:
I can ping P from both server A and Server B. But I just can tnsping Server P from A but not from B.

I checked the tnsnames.ora of A and B. They are identical. And I can tnsping other servers from B.

Any thought about it? Should I configure something on Server P?
Thanks a lot and have a great one!


texts: .Net Application Using Inefficient Query Plan - caused by ARITHABORT setting?

texts:
One of our applications is causing a problem in that there is a stored procedure that returns fine in SSMS, in under 1 sec, but in the application its taking anything up to 10 minutes, depending on the parameters used. Any combination of parameters work fine in SSMS and the execution plan looks good to me.

However, when profiled the Application is clearly using a different, less efficient plan. The SqlClient used to connect has the ARITHABORT setting, set to OFF, which when replicated in SSMS I run into the same performance issues.

I'm guessing ARITHABORT OFF will not allow the optimizer to use the cached plan? Or does it run a separate plan?

According to the Apps guys, there is no way to change the SQLClient connection to use ARITHABORT ON

I'm guessing the ARITHABORT setting is a bit of a misnomer here, and it's actually a parameter sniffing thing caused by the optimizer not using the good plan?

I need to force it to use a good plan regardless, so how best to address the issue? Do I create a plan guide or optimize the stored proc in some way?

This is SQL Server 2008R2 SP2.


texts: Error while executing SSIS package which contains Script component through SQL Server Agent Job

texts:
I have written one SSIS 2012 package which is fetching records from CSV and put it into staging DB and from staging DB , using SSIS script component task, I am inserting data into Dynamics CRM. So far it is working fine when I manually execute SSIS package.

but when I run the SSIS package using SQL server agent job, I encounter below error. After seeing below error at step "Insert TLM Headers Script Component " I understand that it is failing at script component which I have used in SSIS package, but I am not sure what is the cause behind that? please advise.

Error:

Executed as user: domain\user account. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5556.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  1:21:41 PM  Error: 2015-02-25 13:21:45.94     Code: 0xC0047062     Source: Insert TLM Headers Script Component [205]     Description: System.ServiceModel.FaultException: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the  configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.    Server stack trace:      at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)     at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)  End Error  Error: 2015-02-25 13:21:45.94     Code: 0xC0047022     Source: Insert TLM Headers SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Script Component" (205) failed with error code 0x80131501 while processing input "Input 0" (215). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:21:41 PM  Finished: 1:21:45 PM  Elapsed:  4.009 seconds.  The package execution failed.  The step failed.

Thanks
Pankaj


texts: BigQuery - load data with changing schema

texts:
I have situation when an external system is providing data to BigQuery instance as JSON files.
They are rows, that have been updated in that external system, I need this data in BQ for later processing.
It is not a full snapshot of data, only changed rows.
I need to get this data from the external system, periodically join with data I already have in BQ and export for another system.
The problem occurs in a situation, when source external system changes they database schema - it's NoSQL database, they can add some columns. They can do it in every moment without notifying us about it. And then we are expected, to export data with new column already in export.
Any ideas on how to handle this situation? I was wondering about creating a new table with every load and then use a wildcard query, but it will not work when tables that are wildcarded have different columns structure.

EDIT to make question clearer.
I am loading JSON input files into BigQuery. There can be added new columns to the JSON structure at any time.
I need to load all incoming data and then have the possibility to query all of it as with the latest schema I received in the last JSON file. So like dynamically "add" missing columns to old data.


texts: script task sssis file extension is missing?

texts:
Hi I have problem with script task C# I create I have to prefix log file with date but my problem is not adding file extension
log file is log_yyyy_MM_dd without extension
I want ti extension
log file is log_yyyy_MM_dd.txt or CSV

    private string ReadVariable(string varName)
    {
        object rtnValue = null;
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForRead(varName, ref var);
            rtnValue = var[varName].Value.ToString();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
        return rtnValue.ToString();
    }

    private void WriteVariable(string varName, object value)
    {
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForWrite(varName, ref var);
            var[varName].Value = value;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
    }

    public void Main()
    {
        //Read Variables
        String sFlatFileConnection = ReadVariable("FlatFileConnection");

        //Get Run Date
        DateTime dtRunDate = DateTime.Now;

        sFlatFileConnection = sFlatFileConnection + dtRunDate.ToString("yyyy_MM_dd");
        WriteVariable("FlatFileConnection", sFlatFileConnection);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
}


texts:
I have two schemas in my oracle database: XXX_MONITORING and XXX_MONITORING_CONFIGURATION. I had a script running on MONITORING_CONFIGURATION which is run with SQLPlus to update the database.

I split this script into two parts where the new part (lets call it part one) is run on XXX_MONITORING, and contains only a XXX_MONITORING.MONITORING package body replace. I also changed the package body so that it has three new functions.

Now the part one tries to be run, but it gets stuck and I get a timeout message.

I tried to trouble shoot this on the assumption that the problem is database locks, and run the following script:

SELECT s.sid,
   s.SERIAL#,
   s.STATUS,
   s.STATE,
   l.lock_type,
   l.mode_held,
   l.mode_requested,
   l.lock_id1
FROM   dba_lock_internal l,
   v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE '%MONITORING%'
AND l.lock_type = 'Body Definition Lock';

After killing some sessions alter system kill session '51,20351';, as a result I get:

397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING_WEB_SELF_IVR
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING_WEB_SELF_IVR
397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.MONITORING
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.MONITORING
397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING
199 1171    ACTIVE  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING

The sessions are still there after waiting half an hour.

How can I recover from this situation? Should I have updated the package header before adding new functions to the MONITORING package body?

The application using the database is shut down. There might be scheduled database jobs running.

When I run the script one in SQLPLus, the output of the sessions-script changes:

397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING_WEB_SELF_IVR
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING_WEB_SELF_IVR
397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.MONITORING
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.MONITORING
447 5465    ACTIVE  WAITING Body Definition Lock    Exclusive   None    XXX_MONITORING.MONITORING
397 33707   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING
51  20351   KILLED  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING
199 1171    ACTIVE  WAITING Body Definition Lock    Null    None    XXX_MONITORING.DATA_CAPTURING


texts: I was asked this question at an interview and had no answer. Can anyone here explain?


texts:
The Oracle documentation includes the following advice:

  A bitmap index should be built on each of the foreign key columns of
  the fact table or tables

In that reference, there is even a bitmap index on the date column. Whatever happened to cardinality rule for using bitmap indexes? Date columns defy that rule the most, but other columns like customer_key are also a little too huge to be considered candidates for bitmap indexes. I can sort of understand putting one on item_key if you don't have thousands of items.

If not a bitmap index, then what - especially for a date column that has a foreign key to a time dimension - typical stuff - month, year, day, etc? Obviously, it's queried often.

I asked this question on Stack Overflow a couple days ago, but I'm going to delete it, since it received no replies.


texts: Automatic recovery on a 3 node AlwaysOn Multisite Cluster?

texts:
We are looking to build a 3-node AlwaysOn Windows 2012 R2 failover cluster with 2 nodes in our primary datacenter for HA, and a 3rd node replica in our DR datacenter for reporting.

Given this is a multisite cluster, is there any way to configure this in a way which automatic failover will occur if the primary and DR sites lose connectivity or if the primary site goes down entirely?  Or would we have to add a 3rd site with a file share witness to the mix to make that possible?


texts:
I have a mysql server with 5.5. I tried to enable the binlog using the below config file.

# BINARY LOGGING #
server_id                      = 1
log_bin                        = /mysql/binlog/mysql_bin
expire_logs_days               = 14
sync_binlog                    = 1
max_binlog_size                = 100M

/mysql/binlog/ is owned by mysql:mysql

After restart mysql I got the below error.

mysqld: File '/mysql/binlog/mysql-bin.index' not found (Errcode: 13)

So I tried this command.

added this to my.cnf

binlog_do_db                   = mydb
log_bin_index                  = /mysql/binlog/mysql-bin.index

And executed this commands.

touch /mysql/binlog/mysql-bin.index
chown mysql:mysql /mysql/binlog/mysql-bin.index

But still its throwing the same error.


texts: SQLite function to specify exactly how the data is stored internally

texts:
I have used the typeof() function within SQLite before (http://sqlfiddle.com/#!7/fcf0e/1), but I would like to go a little further this time.

I know that INTEGER can be stored internally as 1, 2, 3, 4, and 6 bytes.

What I am wondering is if I can use a function to identify how the value is actually stored internally?

I am looking for something more than just INTEGER, something like INTEGER 4-byte would be great or just 4-byte


texts:
Below I have an MySQL database where "id" is just an id for each row. "question" shows the id of the question. There are four questions, 11, 12, 13 and 14. For every question the user has four answer options (1,2,3 and 4) which is stored in the "answer" column. The "user" column indicates what user who answered. In this example we have user 10, 11 and 12

id      question    answer      user
1           11          2       10
2           12          2       10
3           13          3       10
4           14          4       10
5           11          2       11
6           12          2       11
7           13          4       11
8           14          1       11
9           11          2       12
10          12          2       12
11          13          1       12
12          14          1       12

Let's say that user 10 is the reference user which means that I want to know how well user 10 matches with the others. Using SQL and/or php code how can I match the answers of the users such that I get the matches in percent with the highest percent shown first. So in this example I'm looking for something like.

The Matches of user 10

        user     percent
1       11       50%
2       12       75%


texts: How to implement Draft functionality, with attached files?

texts:
Should allow for:

User can save an entity as a draft, that would normally not be valid in its table (ie some missing values)
User can attach files to the draft
User can find, sort, and discard their drafts

This is not a homework question. I have some ideas, but wanted to see what is out there first. Thanks!


texts:
I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere).

Merge Join:

Inserting and Updating works fine, however, deleting a record from the flat file does nothing.

I guess it's something with the Conditional Split: delete ISNULL(gid_csv) && !ISNULL(gid)

or the following SQL Command: DELETE FROM dbo.gusers WHERE gid = ?

The OLE DB Source and the three destinations are accessing the same table.

What do I miss?


texts: mySQL Workbench new Connection : There is no disk in drive. Please insert disk in \Device\Harddisk2\DR2

texts:
I'm trying to migrate the data at my company from Excel to mySQL so I'm very new at this. I just installed the latest mySQL package via the standard installer and when I try a new connection (I'm trying to get through the mySQLTutorial) the error pops up:

MySQLWorkbench.exe - No disk

There is no disk in drive.
Please insert disk in drive \Device\Harddisk\DR2

My setup for the new connection is as follows:

Connection name: local
Connection method: Standard (TCP/IP)

Hostname: 127.0.0.1
Port: 3306
Default schema: ""

Anyone have any ideas? I'm completely at a loss here...


texts: How can get this value with json sql server?

texts:
How can I get question_id field in this json file? I tried, but it returns null.

DECLARE @json NVARCHAR(MAX)
SET @json =
  N'{
            "solution": "xxxxxxxxxxxxxxxxxxxxx",
            "options": [
                {
                    "choice_id": 205073,
                    "choice": "aaaa"
                },
                {
                    "choice_id": 205074,
                    "choice": "bbbb"
                },
                {
                    "choice_id": 205075,
                    "choice": "cccc"
                },
                {
                    "choice_id": 205076,
                    "choice": "dddd"
                }
            ],
            "question_id": 12345
        }'

SELECT * FROM
 OPENJSON ( @json, '$.options')
WITH (
              choice_id varchar(8000) '$.choice_id',
              question_id int '$.question_id'
 )

Result


texts: In Sql Server, is there a way to check if a selected group of rows are locked or not?

texts:
We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended periods of time (as it is waiting to get locks on all the rows or page lock or table lock) resulting in timeouts or taking multiple days to complete the task.

So, we are changing the approach to delete small batch of rows at at time. But we want to check if the selected (let's say 100 or 1000 or 2000 rows) are currently locked by a different process or not.

If not, then proceed with delete/update.
If they are locked, then move on to the next group of records.
At end, come back to the begining and attempt to update/delete the left out ones.

Is this doable?

Thanks,
ToC


texts: Are there differences between a Fact Constellation Schema and a Galaxy Schema?

texts:
I am currently working on the two schemes and I'm just not sure if they are the same or if there are differences. In the literature both are often synonymous. Now I've read that there are differences (but I hardly find that on the internet).

Is it true that the difference to the Constellation schema is that the fact table does not have to be hierarchical or otherwise directly connected? - but can be very well (instead of the galaxy schema)?


texts:
I have to define a system where we will be storing Image data and which can have an arbitrary number of attributes for the Image added per client. I have designed the following table

Image

ImageID ImageTypeID Data
1       1           0x123...
2       1           0x123...
3       2           0x123...

ImageType - a predefined list of supported Image types

ImageTypeID IsoCode Name
1           12      Hitachi
2           1       Signotec

PropertyType - metadata for each arbitrary property. Can include columns for IsMandatory, datatype, minlength, maxlength etc but not needed at the moment

PropertyTypeID Name
1              AccountNumber
2              PIN

Property - binds PropertyType with Image

PropertyID PropertyTypeID ImageID
1          1              1
2          2              1
3          1              2

PropertyString - actual value of the property, right now we only care for string values as there is no need to do any computation on the values

PropertyID Value
1          0123456789
2          0123
3          9876543210

Given the above design I can query my database like so

DECLARE @ImageCode INT = 12;
DECLARE @PropertyName1 VARCHAR(64) = 'AccountNumber';
DECLARE @PropertyValue1 VARCHAR(1024) = '201602101821';
DECLARE @PropertyName2 VARCHAR(64) = 'PIN';
DECLARE @PropertyValue2 VARCHAR(1024) = '1234';

WITH prop1 AS (
    SELECT P.ImageID, PS.Value
    FROM PropertyType PT
    INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
    INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
    WHERE PT.Name = @PropertyName1
), prop2 AS (
    SELECT P.ImageID, PS.Value
    FROM PropertyType PT
    INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
    INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
    WHERE PT.Name = @PropertyName2
)
SELECT I.ImageID, I.Data
FROM Image I
INNER JOIN ImageType IT ON I.ImageTypeID = IT.ImageTypeID
INNER JOIN prop1 P1 ON I.ImageID = P1.ImageID
INNER JOIN prop2 P2 ON I.ImageID = P2.ImageID
WHERE IT.IsoCode = @ImageCode
    AND P1.Value = @PropertyValue1
    AND P2.Value = @PropertyValue2;

Each client will have a predefined set of extra Image attributes which will be used to narrow down the search (as shown above). The query itself will be generated based on the configured PropertyTypes.

Can this be further normalized? Is there any other way to provide the flexibility of having arbitrary number of field/values?


texts: Can you switch recovery mode to simple from full in an Always ON cluster setup?

texts:
I need help understanding my options.
Given:

SQL server cluster setup that is maintained by a DBA.
System is NOT in production yet (not handed over to the customer).
Initial high volume push of data that has NO breaks for days
I can stop and start the push of data and change the database at any
point. Basically I can stop all input.

I recommend to our DBA that we switch to simple mode because we are seeing within the activity monitor a pile up of queries in a wait state which eventually causes errors for the application servers.
I've never had this problem before and in a process of elimination I see we are in FULL recovery mode instead of SIMPLE.
I asked for us to switch to SIMPLE from FULL and this is the reply I get.  Is there another option perhaps that the DBA isn't aware of?
How can I help?

"We cannot switch to simple using AlwaysOn in SQL."

Can I turn off AlwaysOn for the initial loading phase or am I out of luck?
--- Addition ----


texts: When to add another lookup table for the same field

texts:
Example

Table Recipe uses a lookup table for Recipe Category;
Table Ingredient uses a lookup table for Ingredient Category;
They are in a Many-to-Many relationship;
A Recipe Category will not be used as a Ingredient Category or vice-versa;

Question
Should it be separate lookup tables, or can I use one table with an extra column to identify if it's an Ingredient or Recipe Category? Does this affect optimization or query speed?
Roughly 100 Records if a single table; separated tables it's 5 and 95. The table will appear more than two times in the same query.
I tested the ideas in the related Q & A Relational tables with same content, custom solution. It's easier to handle data on the frontend side and housekeeping, but data integrity could be an issue if the database grows.
Clarifications

A recipe can be classified by more than one recipe category
An ingredient can be classified by more than one ingredient category
There are 5 recipe categories in the business domain
There are 95 ingredient categories in the business domain
Three examples of concrete recipe categories are Omivorous, Vegetarian, Vegan.
Three examples of concrete ingredient categories are Cereal, Meat, Fish.

I've been testing around with both solutions but with so few records I don't see any improvement or loss of performance. Also when building the front end, it's easier to work with one model and a column than 2 models but it's a minor issue.
Related Q & A on Stack Overflow: One lookup table or many lookup tables?


texts: Transaction log uses (not backups of transaction logs)

texts:
Working with an interesting scenario that quite frankly, I have not had the time to test, and would love some expert insight. I have a question concerning transaction logs, and their possible use for recovery (or anything at all for that matter). Imagine that you are using VSS to take application consistent snapshots of volumes holding both .mdf and .ldf files for the database (storage array driven). The questions I have is:

Given that only VSS snapshots are available (point in time), I am wondering if there is any use in having a database in full recovery (assuming there was a way of truncating the transaction logs as well). So, the setup is: database is in full recovery -> VSS snapshot of database -> truncate transaction logs. Can the transactions in the log be of any use to me if I were to mount a clone of the database?


texts: Update query to Reset counter when date column changes

texts:
column1   column_sec attempt      date    counter
1250       1250_A     1        16-06-2016    1
1250       1250_B     1        16-06-2016    1
1250       1250_C     1        16-06-2016    1
1250       1250_A     2        16-06-2016    2
1250       1250_B     2        16-06-2016    2
1250       1250_C     2        16-06-2016    2
1250       1250_A     3        06-06-2016    1
1250       1250_B     3        06-06-2016    1
1250       1250_C     3        06-06-2016    1
1250       1250_A     4        06-06-2016    2
1250       1250_B     4        06-06-2016    2
1250       1250_C     4        06-06-2016    2

I want to write an update query when my date changes it will reset the counter column as shown above,
Can anyone help ?

So far i have tried this, But it increments counter by 1 till the end of the table.It did not help.

set @v1 := '', @v2 :='',@v3 :='', @num := 1;

select column1,column_sec,attempt,date,
   @num := if(@v1 := date,if(@v2 := column_sec,if(@v3 := column1,@num + 1,1),1),1) as counter
from table_name where date is not null
order by column1,column_sec,attempt;


texts: Postgres inherit indexes for partitioned tables

texts:
I have a table with approximately 60 million rows that I have partitioned by state into 53 sub-tables.  These tables "inherit" the big table like so:

CREATE TABLE b2b_ak (LIKE b2b including indexes, CHECK ( state = 'AK') ) INHERITS (b2b8) TABLESPACE B2B;

My question is this:  if I do not build the indexes on b2b8 until after the copy statement completes, do the sub-tables inherit the indexes?  In other words I want to do this:

Create b2b8
Create b2b8_ak inherits b2b8
COPY b2b8 FROM bigcsvfile.csv
CREATE INDEX CONCURRENTLY

And have the whole thing turn out to have created all of the indexes on the sub-tables.


texts: How can a MySQL delete statement affect a table with over 30 millions records?

texts:
I have to delete about 28 million records from a InnoDB table in MySQL.
I am wondering what happens if I am doing a select statement on the same table during the delete operation.
I guess the delete will take some time to complete.

I am using MySQL version 5.6.
I don't want to delete all records I will have a where condition or maybe having.
Thank you for your time.


texts: Can we downgrade from 19c to 12.1.0 if the compatibility is set to 12.2.0

texts:
Team:
Recently i upgraded a 12.1.0 database to 19c (non CDB architecture). Current compatibility is set to 12.2.0. Can I downgrade it to the earlier version - 12.1.0? Documentation states the downgrade is possible but am not sure about the compatibility parameter.
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/downgrading-oracle-db-after-upgrade.html#GUID-59F92E2A-DC4D-46A3-9AC9-63F47BE4A86F


texts: Why is it considered that a set unnormalizes a database absolutely?

texts:
In order for a relationship to be on 1NF it needs to have all values as atomic, if there's a set it's not even in that first normal form:

But intuitively I think that a table with that set would be more normalized than one that is not as long as the values of that set are just used as attributes of the entity.

For example, let's imagine this table about paintings:

Painting_name, Author, Used technique, Used colors

Now if we get to use a set of colors like {blue, Green, yellow, black, White, purple} we get a table that is not even in 1NF.

If we pass the table to 1NF, then we need to have 6 rows each one repeating the Painting_name, Author, and Used technique.

This looks way more unnormalized than the table that is not even in 1NF, and I don't see why having a set there would harm any possible normalization, as that sets would only be used in that table.

What is then the reason that atomic values are needed in order to have a normalized table?


texts: Cast to date is sargable but is it a good idea?

texts:
In SQL Server 2008 the date datatype was added.

Casting a datetime column to date is sargable and can use an index on the datetime column.

select *
from T
where cast(DateTimeCol as date) = '20130101';

The other option you have is to use a range instead.

select *
from T
where DateTimeCol >= '20130101' and
      DateTimeCol < '20130102'

Are these queries equally good or should one be preferred over the other?


texts:
My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory. Then MySQL goes through all records on that block to find that specific record. Is that correct ?

I believe default block size where MySQL write on disk is of 16KB(from google).
Also from diff sources on google I came to know on an average time taken to read 100MB of data from disk(HDD) is around 1 sec . It means an an average seek time to read the block should be around  16/(100* 1000) secs. correct ?


texts: Does SQL Server 2014 Express, with Advanced Services, actually support full text search?

texts:
I've installed SQL Server 2014 Express edition, with advanced services. I was hoping to try out the full-text search features. I read here that full text search is supported by the 2014 express edition. But, after installing and trying to create a full text index, I get an error stating that full text indexes are not support in this version of sql server.

Are full text indexes actually supported or not? Is there a chance I'm installing the wrong version (not advanced services)? I've uninstalled and re-installed two times just to be sure, but same issue both times. I'm positive I'm installing using the advanced services installer.

I'm on Windows 7, 64-bit.


texts:
I'm doing some research about how to troubleshoot local memory pressure. Correct me if I'm wrong but as I understand it is that SQL Server uses a CLOCK algorithm for the cache- and userstores. Each clock has two hands, an external and an internal.

My interest is for the internal clock hand. If a worker that accesses a cache notices that a cache is greater than a certain percentage of memory, the internal clock hand starts to free memory for that cache.

Using the dmv dm_os_memory_cache_clock_hands I can see information about the clock hands. The column rounds_count represents how many times the clock hands scanned the entire cache. last_tick_time is the time the hand last moved. round_start_time is time of the last sweep.

What is the difference between last_tick_time and round_start_time? Is the cache not cleaned when the hand is moved?

How many entries get removed with each tick of the clock?

Can somebody helps me understanding the working of this clock algorithm in SQL Server?


texts: SQL, creating a new table with auto generated passords from other tables

texts:
i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto generated passwords.

Is this possible?

SELECT
    CurrentPupil.SchoolID AS 'SchoolID',
    CurrentPupil.PupilID AS 'PupilID',
    PupilPersonalDetails.Surname AS 'Surname',
    PupilPersonalDetails.Forename AS 'First_Name',
    PupilPersonalDetailsGenderLookupDetails.Description AS 'Gender',
    ContactMaster.LastName AS 'Contact_Last_Name',
    ContactMaster.FirstName AS 'Contact_First_Name',
    ContactMasterGenderLookupDetails.Description AS 'Contact_Gender',
    ContactMaster.LastName2 AS 'Contact_Last_Name_2',
    ContactMaster.FirstName2 AS 'Contact_First_Name_2',
    ContactMasterGender2LookupDetails.Description AS 'Contact_Gender_2',
    ContactEmailInformation.EmailID AS 'Contact_Email_Address',
    PupilCurrentSchoolYearGroupLookupDetails.Description AS 'Year_Group',
    PupilCurrentSchoolFormSchoolLookupDetails.Description AS 'Form',
    ContactMaster.ContactID
FROM
    win2k12engage.EngageSpratton.dbo.CurrentPupil
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilPersonalDetails AS PupilPersonalDetails ON CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID
AND CurrentPupil.PupilID = PupilPersonalDetails.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilPersonalDetailsGenderLookupDetails ON PupilPersonalDetailsGenderLookupDetails.LookupDetailsID = PupilPersonalDetails.Gender
AND PupilPersonalDetailsGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilContacts AS PupilContacts ON CurrentPupil.SchoolID = PupilContacts.SchoolID
AND CurrentPupil.PupilID = PupilContacts.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactMaster AS ContactMaster ON PupilContacts.ContactID = ContactMaster.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactEmailInformation AS ContactEmailInformation ON ContactMaster.ContactID = ContactEmailInformation.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGenderLookupDetails ON ContactMasterGenderLookupDetails.LookupDetailsID = ContactMaster.Gender
AND ContactMasterGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGender2LookupDetails ON ContactMasterGender2LookupDetails.LookupDetailsID = ContactMaster.Gender2
AND ContactMasterGender2LookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilCurrentSchool AS PupilCurrentSchool ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID
AND CurrentPupil.PupilID = PupilCurrentSchool.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilCurrentSchoolYearGroupLookupDetails ON PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID = PupilCurrentSchool.YearGroup
AND PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.SchoolLookupDetails AS PupilCurrentSchoolFormSchoolLookupDetails ON PupilCurrentSchoolFormSchoolLookupDetails.LookupDetailsID = PupilCurrentSchool.Form
AND PupilCurrentSchoolFormSchoolLookupDetails.LookupID = '1002'
WHERE
    ('%wc%' = '%wc%')

*************************************UPDATE***************************
Hi Guys back to my desk. I am adding one additional column to the current query and have that additional column contain a random string of data that would be a password...also
the tables / columns above are from a database that i cannot write to but only read, so i use the win2k12engage.EngageSpratton.dbo.fieldname to connect to my other database Eletter.dbo.newtable where i want to have all the fields above plus one more column with the password.

Cheers

Many thanks in advance
Vitor