texts: SQLCMD Mode in SSMS and @@variable expansion

texts:
When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL variables.

Problem definition

I want to use the power of SQLCMD's variable expansion to substitute environment specific values in our deploy scripts instead of the existing tsql string building mash I've walked into. With the one exception of current environment, the use of SQLCMD to handle deploys has gone extremely well.

--
-- define 2 sqlcmd variables that will be expanded in scripts
--
:setvar dbServer "DEVA2\DEV2"
:setvar dbNotServer @@servername

SELECT
    '$(dbServer)' AS hard_coded_value
,   @@servername AS [servername]
,   '$(dbNotServer)' AS dbNotServer

And that generates the following results.

hard_coded_value  servername  dbNotServer
DEVA2\DEV2        DEVA2\DEV2  @@servername

Meat Loaf says 2 out of 3 ain't bad, but I'd rather have a 3 out of 3 solution.
When that script gets deployed the test server, I don't want to trust the deployment folks with editing the script.

If the only solution to using SQLCMD is to invoke scripts completely from the command line, I can accept that but wanted to throw this out here as I'm green to using SQLCMD.

Desired output

:setvar dbNotServer @@servername
SELECT '$(dbNotServer)' AS worked

Results

worked
DEVA\DEV2

Fruitless pursuits

The first BOL link showed promise, all I had to do was use the SQLCMDSERVER but to no avail. Run within the context of SSMS in SQLCMD mode, it will throw a fatal scripting error

-- A fatal scripting error occurred.
-- Variable SQLCMDSERVER is not defined.
SELECT '$(SQLCMDSERVER)' AS [FatalScriptingError]

Tumbleweed update

2011-08-12
In an attempt to reduct my problem to the simplest form, based on the answers, I oversimplified my queries (my apologies). A portion of the query I used is below. The two reponders are correct in their answers that identify wrapping @@servername in tick marks results in the literal value and to get the expanded value, I'd need to unwrap it from the quotes. My desire was to use sqlcmd variable substitution rather than string building which unwrap would entail.

INSERT INTO
    dbo.SSISCONFIG
(
    ConfigurationFilter
,   ConfiguredValue
,   PackagePath
,   ConfiguredValueType
,   Application
,   Category
,   Subcategory
,   Comment
)
SELECT
    'Default.Accounting' AS ConfigurationFilter
,   'Data Source=$(dbServer);Initial Catalog=Accounting;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[Accounting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment
SELECT
    'Default.$(sqlVersion).CorporateReporting' AS ConfigurationFilter
,   'Data Source=$(dwServer);Initial Catalog=CRDS;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[CorporateReporting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment

Command line invocation works (as expected)

C:\>sqlcmd -E -d master -S DEVA2\DEV2 -Q "SELECT '$(SQLCMDSERVER)' AS [servername]"

I've tried and discarded other permutations of the :setvar x @@servername in an attempt to get the evaluated value of the database variable stored into the SQLCMD variable. At this point, I'm fairly certain the sqlcmd variables are susbstituted in queries prior to query compiliation but would love to be proven wrong.

BOL references

Using sqlcmd with Scripting Variables
Editing SQLCMD Scripts in the Transact-SQL Editor
Using the sqlcmd Utility
Tutorial: sqlcmd Utility
sqlcmd Utility


texts: Calculating the elapsed time of Oracle Database Startup operation

texts:
I'm trying to calculate the elapsed time of Oracle Database startup command. The view V$INSTANCE contains the STARTUP_TIME column which tells at what time the database is started. But, i need way to find the time elapsed from the time we enter startup till the database is opened.

Since, we can't query SYSDATE or SYSTIMESTAMP when the database is not open, im finding it difficult to come up with a logic.

Is there any other way we can get the elapsed time for startup operation?


texts: Starting up postgres on Ubuntu for the first time

texts:
I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions:

postgres --coordinator -D /usr/local/pgsql/data -p 1281

But I get the following issue:

LOG:  database system was interrupted; last known up at 2014-07-08 12:17:38 BST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/17FCE80
LOG:  redo is not required
FATAL:  could not open lock file "/var/run/postgresql/.s.PGPOOL.6667.lock": Permission denied
LOG:  pool manager process (PID 1214) exited with exit code 1
LOG:  terminating any other active server processes
LOG:  startup process (PID 1213) exited with exit code 2
LOG:  aborting startup due to startup process failure

Even though I am logged in via su postgres  and I have chown postgres /var/run/postgresql. Any ideas?


texts:
Table A

    > - Product#|Customer|state|Type
      Glasses |mr. Xyz | MN |Instore
      Glasses |mr. abc | MN |Instore
  perishables|mr. abc12| TN |Instore
 N-perishables|mr. abc122| GN |Instore

Table B

> - Product#|Name|state|Type
  Glasses |mr. fff | MN |online
  Glasses |mr. abc | MN |online
perishables|mr. abc12 | TN |online
 Non-perishables|mr. abc122 | GN |online

    `

Trying to create new column and then do comparing?
Condition, if the type is in-store they are never new customer, if they are online they could be new customer only if their record do not exist in In-store Type.

o/p

Type|customer| New Customer?
Instore|mr. Xyz  |No
Instore|mr. abc  |No
Instore|mr. abc12|No
Instore|mr. abc122|No
Online| mr.fff    |Yes -"as it's record is not available  in-store type, hence it will be labelled as new customer"

Query tried:

select
       `Customer`,
    CASE
    WHEN `customer` in (select `Customer` from `Table B`)  THEN 'Yes'
    ELSE 'NO'
END AS 'new customer'
FROM Table A`

Please give me optimized query, as its huge data in millions.


texts: Pull every other row, starting at 0, and concatenate them together

texts:
I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other row starting at 0 (so 0, 2, 4, 6, etc.) and then concatenate the values of Text together. I found https://stackoverflow.com/questions/4799816/return-row-of-every-nth-record which looks to be how to get every other row but I am not sure how to do the concatenation.

Example Table:

Row ID Text
0   0  This
1   1  Do
2   2  is
3   3  not
4   4  the
5   5  use
6   6  text
7   7  text

The result I would want from that table is This is the text.


texts: Database Full Backup Maintenance Task has large delay between databases

texts:
I have several database servers that use the same basic maintenance task to do daily full backups over night. Intermittently the backup job which normally takes 4 or 5 hours will suddenly take 13 to 15 hours. At first I figured the network was busy or the operations team started their nightly processing early, but I've been digging into this further and I noticed that something odd.

The maintenance task is set to do a backup of all databases ignoring those that are offline. It saves the backups to a Data Domain server.
This is what is expected to happen and usually does:

At 9pm the backup job starts.
System databases backup first and take a combined 2 minutes
DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
DB2 backup starts and takes 2.5-3.5 hours
Job finishes between 1-3 am

Looking at the backup set history in MSDB this is what I'm seeing

At 9pm the backup job starts.
System databases backup first and take a combined 2 minutes
DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
tsql command to start backup of DB2 begins executing
7-9 hour delay
DB2 backup actually started, according to MSDB.dbo.backupset history, and takes 2.5-3.5 hours
Job finishes between 8am and 12pm

As far as I can tell there is no activity before 2am that should be causing locks or contention, and the delays appear to be happening irregularly

My Question is this, what could be causing this large delay from when tsql is executed to when the backup is actually started?

Edit
Rough Server Specs:
VM Ware Virtual Machine (All resources dedicated)
Windows Server2012 R2
SQL Server 2014 SP2
CPU: 16 total cores
RAM: 225GB


texts: test data to generate high index fragmentation level

texts:
I want to test the rebuild and reorganize operations for the indexes in sql server and for that I need to populate the db with test data that generates high fragmentation levels/percentages. Does anyone have a hint for me on how to do this? I heard about backwards insert, does this hep? If yes, how can I achieve this?
Many thanks!


texts: How to rearrange columns in Oracle SQL Developer script output

texts:
maybe this is an already answered question, but I am relatively new to Oracle DB and couldn't find my answer around the Internet.
So I have created a table in Oracle and when running the SELECT statement the output shows me columns like this:

Please if anyone can explain how to rearrange the columns so, the values from CITY column go under the City column, and the Customer_Name goes under that output column. Thanks in advance!


texts: how to generate the creation script for partition function and partition schema?

texts:
I have a script to generate the create table script. You can see it on my answer to this question.

However, now I need to script my partition functions and partition schemes

On this question here, see the answer has examples on how to add a partition to a table and how to remove a partition from a table, and I would like to have a look at the scripts at any stage.

I have found this link: how to find partition function text applied to a table

how to generate the scripts for create partition function and partition schema?

I was working on a script to generate the create partition function
as you can see below, but it only works for my own PF_year partition function
because it was tricky to get over the fact that sys.partition_range_values has a sql_variant data type as value.

when I used a case I got the following error:

  Msg 206, Level 16, State 2, Line 35

  Operand type clash: int is incompatible with date

Then in his great answer Dan Guzman, showed us SQL_VARIANT_PROPERTY some wonderful thing that I was not aware of, now I am a new DBA and person.

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT
    RADHE = 'CREATE PARTITION FUNCTION' + space(1) + quotename(spf.name) +
'(' +
    COALESCE(baset.name,'data type not found') +   ')' + CHAR(13) +
    'AS RANGE ' + CASE WHEN CAST(spf.boundary_value_on_right AS int) = 1
THEN 'RIGHT ' ELSE 'LEFT ' END  + CHAR(13) +
    'FOR VALUES (' +
    (SELECT STUFF(
    (SELECT
    [text()] = N',''' + CAST(
                      CASE st.system_type_id
                       WHEN 40 THEN CONVERT (date, sprv.value,126)
                       --WHEN 56 THEN CONVERT (int,  sprv.value)
                       --ELSE CONVERT (int,  sprv.value)
                      END
                      AS NVARCHAR) + ''''
    FROM sys.partition_range_values sprv
    WHERE sprv.function_id=spf.function_id
    order by sprv.boundary_id
     FOR XML PATH('') ), 1, 1,N'')+ N');')

    ,st.system_type_id
    ,spf.name AS [Name],
    spf.function_id AS [ID],
    CAST(spf.boundary_value_on_right AS int) AS [RangeType],
    spf.create_date AS [CreateDate],
    spf.fanout AS [NumberOfPartitions]
    FROM
    sys.partition_functions AS spf

    INNER JOIN sys.partition_parameters AS spp
            ON spp.function_id=spf.function_id

    INNER JOIN sys.types AS st
            ON st.system_type_id = st.user_type_id
           and spp.system_type_id = st.system_type_id

    LEFT OUTER JOIN sys.types AS baset
                  ON (     baset.user_type_id = spp.system_type_id
                       and baset.user_type_id = baset.system_type_id)

                  or (     (baset.system_type_id = spp.system_type_id)
                       and (baset.user_type_id = spp.user_type_id)
                       and (baset.is_user_defined = 0)
                       and (baset.is_assembly_type = 1))
    WHERE spf.name = 'PF_YEAR';


texts: Compressing partition - what about index?

texts:
To save space on disk I want to compress some partitions of my tables:

alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations;

I have also indexes on this partition.
Should I recreate indexes after compression, or is it not necessary?


texts: Connect to SQL Server using windows authentication from another PC without Active Directory

texts:
I have SQL Server 2008 R2 Express installed on a PC.

I have some other PCs that are connected to this PC on a local network.

Currently, those PCs connect to SQL Server using SQL Authentication, however I want them to connect to SQL Server using Windows Authentication.

We don't have an Active Directory domain.  All PCs are part of the "WORKGROUP" workgroup.


texts: What's the difference between data definition language and data description language?

texts:
Are they the same thing? Which one does DDL stand for? Here's what my understanding is, but could potentially be incorrect:

Data definition language:
Contains names and types and field length etc. of the attributes in a table

A database about a database - stores metadata

DBMS refer to these during the running and maintenance of a db

Data description language:

Function used by RDBMS to craete a database from nothing

Defines a database's table using Validation rules, field types, auto-incrementing requirements, primary kets etc.

Results in a final table design, aka  a schema

It's likely that I've gotten it all messed up, would appreciate some wisdom on it. Thanks.


texts: CSV data export to SQL Database with double quote problems

texts:
I am trying to import data from falt files (.CSV) into SQL table. I am able to import them no problem but the data is coming with double quotes. What is the best way of resolving this problem?

Regards,


texts: Designing an Accumulating Snapshot with Repeated Groups, need advise

texts:
Situation

I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School.

At the student level it has to show the trend of the student over a set of periods: "I1", "G1", "I2", "G2", "I3", "G3", "I4", "G4"

The pie chart will show a number and pct of students with 1,2,3, or none risks factors. Risk factors being: Academics, Discipline, and Attendance. I have been given a formula to determine if a student is at risk at any of the categories above.

We have a Ralph Kimball based approach Data Warehouse.

I have a working query at the school level that I am able to plug the given formula through subselects and sums/counts that returns me as follows:

STUDENT_ID  STUDENT_NAME  PERIOD SCHOOL_YEAR  ACD_RISK  DISC_RISK  ATT_RISK
1           Viriato       I1     2010/2011    1         1          1

1           Viriato       G1     2010/2011    1         0          0

2           Teodosio      I1     2010/2011    0          1         0

.....................

The query that produces me the total number of students having 1,2,3 or 0 risk factors by doing a count sum on the output above works fine as far as performance is concerned. However when I run it at the region and district levels, performance is not acceptable.

Solution

I need to create an Accumulating Snapshot

Concerns

Ralph Kimball in "The Complete Guide to Dimensional Modeling" chapter 12 page 244 states on Accumulating Snapshot:

  A single row represents the complete history of something.

Solution A: Prior to Reading Kimball

So should I have a job that produces a table with the resultset above. But then I would have 8 rows per student per year. I would not have a single row like Kimball suggests.

Solution B: After Reading Kimball

student_id,

name,

school_year,

period,

att_risk_I1,

adm_risk_I1,

disc_ris_I1,

att_risk_G1,

adm_risk_G1,

disc_ris_G1,

.

.

.

.

att_risk_G4,

adm_risk_G4,

dis_ris_G4

As you can see with solution B I would have one row per student per year, and I could generate a trend graph for that student all the way from elementary school to high school with 12 rows (Grades 1st through 12th). However I am not sure I like having ATT_RISK, DISC_RISK and ADM_RISK repeated 8 times.

Which approach would you take?


texts: Invoke-SqlCmd - does casting as integer prevent injection

texts:
I am lazy and would rather parameterize once (to get an object_id for a name) and use Invoke-SqlCmd for the rest of the work.

Given a series of functions meant to work with an arbitrary server object, is the below test enough to confirm that CmdletBinding an input parameter as Int32 prevents injection by way of the user-supplied $object_id?

function foo {
    [CmdletBinding()]Param(
        [Int32]$object_id
    )
    $query = "select type_desc from sys.objects where object_id = $object_id;"
    Invoke-Sqlcmd -ServerInstance "localhost" -Database "tempdb" -Query $query
}

foo 3
foo "0 union all select name from sys.syslogins where sid = 0x01"
foo $null
foo ([math]::Pow(2,31)+1)
foo @(1,2)


texts: SQL Server Express Edition feature and upgrade concerns

texts:
I want to install and learn T-SQL with SQL Server. The lightest edition that I can find is the Express Edition 2017.

Will the express edition support procedures, triggers, dynamic sql, PIVOT, sequences, dynamic management views and functions?
Will it be upgradable to adding ssis, ssrs and ssas in future (I read documentation on Microsoft community blogs, but couldn't find an answer specific to this edition), maybe I will need them in future.


texts: Can you rollback a query in state 'committing alter table to storage engine'

texts:
We've got an InnoDB table with 70 million rows, and we have been trying to run an alter table statement to modify and add a couple of columns. The query seems to have altered the table, and is now in the state of 'committing alter table to storage engine'.

START TRANSACTION;
ALTER TABLE table
  MODIFY COLUMN column1 int(11) NOT NULL DEFAULT 0,
  MODIFY COLUMN column2 tinyint(1) NOT NULL DEFAULT 1,
  ADD COLUMN column3 int(11),
  ADD COLUMN column4 int(11) NOT NULL DEFAULT 1,
  ADD COLUMN column5 varchar(255);
COMMIT;

This has been running overnight, and is at 19 hours at the current time. We do not have the performance schema enabled so cannot look at an estimated time of completion. My concern lies as to what the query is doing and whether the query will rollback if killed. I have seen other questions relate to queries that are stuck in copying to tmp tables, or awaiting a table lock. However I cannot find anything about being stuck while the alter table is committing.

Is it safe to kill a query in this state, and if the query is killed, will it rollback successfully?

The server is running MariaDB 10.2


texts: What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6?

texts: Warming up a cold MySQL instance is moving all data or the data with the highest probability of getting used to main memory. And, yes, by main memory, I mean InnoDB Buffer Pool. The Buffer Pool is configured as a large chunk of main memory, usually. Getting all the data from disk to main memory will take time. Rather than leaving it for the future, can the buffer pool be loaded in advance with the data most expected to be used at the earliest?


texts: VIEW instead of FUNCTION for inserting data

texts:
I have a separate schema called "api" where all my "front-end" functions reside. My API server only have access to this schema. This works great for getting information out of the database in a controlled way.

To insert data in a controlled way I can either create functions in the api schema. But functions with many parameters (i.e. all fields to be inserted) are cumbersome to work with because:

It's easy to mix up parameters in calls
I don't know of any syntax to call functions with named parameters

So, I'm thinking of creating "proxy-views" in my api schema instead. The views would show no data (i.e. WHERE false). The API server would do ordinary inserts into these views and a trigger would take care of the actual insert logic.

The code for inserting data with INSERT statements are more readable than calling functions with a list of unnamed parameters.

Have I missed anything?
Is this a bad idea?
Maybe there is someway to name the input parameters for functions?

Thanks!


texts:
Good morning All,

Trying to find dependencies on specific tables.  Used

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE...

The above query appeared to confirm my assumption; however when I ran

SELECT *
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME LIKE...

The results were inaccurate!

I checked the definition of some of INFORMATION_SCHEMA.VIEW_TABLE_USAGE, and it doesn't reference the tables in question.  Why is it wrong?!

Already checked out this post - Thanks Aaron Bertrand!

INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog


texts:
I'm new to data management. We are currently creating an ER diagram. First, I will ask a question and then give you an example.
Question
Can I connect 3 or more entities by one connection?
Task
Situation is university. We have groups, students, head of a group (I don't know what you call him. He is one of the students in group, and has certain duties. Each group has 1 head of a group).
Also we have classes, teachers and tasks. One teacher can have many classes and one class may be taught by many teachers.
Tasks are given to a certain group on a certain class by a certain teacher.
Not in diagram, in real world, teacher connected to a task and to a class. And these 3 objects can be presented as 1 object. And these connection of teacher, task and a class must be connected to group. That is what our teacher told us.
That sounds logical, but I can't think of a way to do that in ERD. What would you do?


texts: Convert MyISAM to InnoDB in PXC master master

texts:
I have 3 nodes PXC 5.6 master master cluster. I have few tables that are using MyISAM.

Total MyISAM table = 120
Total MyISAM table size = 80MB

I have also read few recommendations where it suggests not to run ALTER command on cluster.

How can I safely convert those tables to InnoDB without risking the cluster?


texts: What is the best way to automate a migration of multiple SQL Databases to a different hard disk?

texts:
We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names all remain the same.

What is the best, safest way to do this in an automated fashion?

My initial idea was to use a sort of pseudo ForEach Loop, supply a list of database names, and have it loop through queries to backup and restore or detach and re-attach the databases, but I have not been able to properly insert the database name variables into SQL queries within a loop.


texts: How do I group results in batches of consecutive rows with the same 'module'

texts:
I'm looking for a seemingly very simple SELECT.

I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text).

Actually, I care only about the first two columns, i.e. which module has been called at all (which is the name of a procedure in a PL/SQL package). The date I only need for ordering (ASC) the result, and one could also use the ID column for the same purpose, which is an auto-incremented integer value (Oracle: sequence.nextval).

Since the logging produces waaaaayyyyy too much data, tens to hundreds of PTEXT entries for a single MODULE, I would like to condense the MODULE rows, which seems to call for a simple GROUP BY.

Maybe this illustrates the issue:

I start with the inner query below, which gives me the correct sequence of "module" calls, but does not aggregate them (example 1 above). So my idea was okay, I get the correct result in the correct order - so if I now select just the one column I'm interested in and use "group by" on this ordered(!) data I should get what I want - but adding the "group by" below gives me a COMPLETELY different order. Apparently my knowledge about what "group by" does is inadequate.

SELECT module FROM
(
  SELECT module, pdate FROM logging
  ORDER BY pdate asc
)
--GROUP BY module

Result without "GROUP BY" (first 12 lines):

set_option_value
set_option_value
set_option_value
set_option_value
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...

Result incl. "GROUP BY":

get_fun_AppOptionsSingleVal
get_option_value
get_parameter
get_usecase_customer
get_kungesId_FY_cusper
fill_missing_products
update_chart_budget_uc1
get_Berein
set_hierarchy
get_element_num_value
icas_dyn_create_chart_table
get_xmlTemplate
...

As one can see the "GROUP BY" produces a completely different order.

What I WOULD HAVE WANTED:

set_option_value
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...

Basically, what I want can be achieved very simply by taking the text output of

  SELECT module FROM logging
  ORDER BY pdate asc

and pipe it through "uniq" on the Unix command line. Now I'm just curious why this isn't as simple in SQL.


texts: Should "Teachers" and "Students" be kept in a common "Person" table?

texts:
I've already read these posts:
Storing whether a user is a student or teacher? Is a lookup table necessary?
Creating a database for a scenario involving students, teachers and courses
In a school database, do I need to have repeating data when a student can also be a teacher?
All of them were close, but none are exactly what I want. A few points:

This app will be used by Grade School teachers.
A student will never be a teacher, and a teacher will never be a student.
In addition to Students and Teachers, there will also be contact info for Admins who are neither students nor teachers.
Teachers will always be Users. Admins may or may not be users. (We may store an Admin's name and contact info, but that admin may never have a reason or need to log into the app.)

My thought is to create a "Person" table:

PersonId
FirstName
LastName
Email (NULL if student)
PersonType (Enum: 1 = Admin/Teacher, 2 = Student)
Other Columns as needed

And another table "Passwords":

PasswordId
PersonId
PasswordHash
Other Columns as needed

If a Person is also a user, they will have a value for 'Email' and also a record in the Password table.
Am I on the right track?


texts: How to get records from two Dates in MySql if stored in String Format?

texts: If you store your dates in string format in mySql.How to get the Records


texts:
I made this code for SQL Server to select how many times the rider "AsdoVolante" came first in the first semester of 2017. But it does not work. What could be wrong?
Tables created in SQL Server:
CREATE TABLE Car(
IdCar INT NOT NULL,
Brand VARCHAR(20) NOT NULL,
Model VARCHAR(50),
PRIMARY KEY (IdCar));

CREATE TABLE Rider(
IdRider INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdRider));

CREATE TABLE Team(
IdTeam INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdTeam));

CREATE TABLE Sponsor(
IdSponsor INT NOT NULL,
Name VARCHAR(20)NOT NULL,
Address VARCHAR(50),
PRIMARY KEY (IdSponsor));

CREATE TABLE Race(
IdRace INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
DateRace DATE NOT NULL,
NumbLaps INT NOT NULL,
BestTime TIME,
PRIMARY KEY (IdRace));

 CREATE TABLE TeamJoinsRace(
IdEnrolment INT NOT NULL,
IdTeam INT NOT NULL,
IdCar INT NOT NULL,
IdRider INT NOT NULL,
IdRace INT NOT NULL,
FinalPosition INT,
FinalRaceTime TIME,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
FOREIGN KEY (IdCar) REFERENCES Car(IdCar),
FOREIGN KEY (IdRider) REFERENCES Rider(IdRider),
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
PRIMARY KEY (IdEnrolment));

CREATE TABLE OficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE NonOficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE Mechanic(
IdMechanic INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdMechanic));

CREATE TABLE Tuning(
IdTuning INT NOT NULL,
IdMechanic INT NOT NULL,
IdTeam INT NOT NULL,
TypeTuning VARCHAR(20) NOT NULL,
TimeTuning TIME,
DateTuning DATE NOT NULL,
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
FOREIGN KEY (IdMechanic) REFERENCES Mechanic(IdMechanic),
PRIMARY KEY (IdTuning));

CREATE TABLE Laps(
IdLap INT NOT NULL,
IdEnrolment INT NOT NULL,
RelativePosition INT NOT NULL,
TimeLap TIME,
FOREIGN KEY (IdEnrolment) REFERENCES TeamJoinsRace(IdEnrolment),
PRIMARY KEY (IdLap, IdEnrolment));

Code with the error:
SELECT P.DateRace, Nm.Name, epp.FinalPosition
FROM Race P, Rider Nm, TeamJoinsRace epp
WHERE epp.FinalPosition = (SELECT FinalPosition FROM TeamJoinsRace where FinalPosition = 1)
HAVING Nm.Name ='AsdoVolante' AND P.DateRace BETWEEN '2017-01-01' AND '2017-06-30';

Here is the error that I get:

Msg 8121, Level 16, State 1, Line 14
Column 'Piloto.Nome' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 14
Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 14
Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


texts: SQL Server Partitioning - Parent / Child tables

texts:
I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is often referenced in another table. For example, I have:

*Instances*
Timestamp (partition key!)
LocationID
...

*Events*
InstanceID
...

*Location*
ID
...

Here, instances contains the partition key. This references a location (LocationID > Location.ID). Also, there is the Events table which references the Instance (InstanceID > Instance.ID).

Is there any way I can apply partitioning to this structure without having to denormalise my tables and have the 'Timestamp' column copied everywhere?

I'm using SQL Server 2008 R2. Thanks in advance.


texts:
I need to JOIN two tables to correct the price with exchange rates as

SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date

The problem is that table2 does not have the exchange rate for all dates.

How can I get a nearest exchange rate for missing dates in table2?

EXAMPLE

Table 1

date          price
2019-01-01    2.3
2019-01-03    3.4
2019-01-04    2.6
2019-01-05    2.2
2019-01-06    2.5
2019-01-07    5.1
2019-01-08    4.6
2019-01-09    4.2
2019-01-10    3.3

Table 2

date          rate
2019-01-04    1.1
2019-01-06    1.3
2019-01-09    1.0

Alternatively, an approach to fill the missing dates in table2 works too.


texts: How to use a value from insert in conditional insert in postgres

texts:
insert into addresses(street, city, user_id)
select
      'street1','LA', 2
where exists (select * from users where users.id = $$$$$ AND users.storeaddress='true'))

I want to insert address for a user only if the storeaddress value for him is 'true'.

I want to sql to automatically use 2 in place of $$$$$. How to do this in sql?

If I simply do user_id in place of $$$$$ I get

HINT:  There is a column named "user_id" in table "addresses", but it cannot be referenced from this part of the query.


texts: How to find equivalent of Oracle's privileges in SQL Server?

texts:
I have a user in Oracle for which the permissions given are CONNECT and SELECT_CATALOG_ROLE.

How do you find the equivalents when setting up a similar user in SQL Server?


texts: Performance Metrics: Columnstore Indexes vs SSAS Cubes

texts: What has faster performance speed:  Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies conducted by Microsoft?


texts: Does range improve match operation and which runs first, range or match

texts:
I am using MySQL version 5.6.10

I have a very very large table large_table and a relatively small table small_table

The small_table has only the records of the date 2019-11-20

There is a column I want to update in small_table whose entry is in large_table. I have a unique key using which I would find the row and then get the column from large_table and update it in `small_table.

I have run the following query

update
    small_table, large_table
set
    small_table.start_time = large_table.start_time
where
    small_table.uuid = large_table.uuid
;

The above query is taking very very long. I was wondering if I can improve the query by specifying the range of date in large_table like

update
    small_table, large_table
set
    small_table.start_time = large_table.start_time
where
    small_table.uuid = large_table.uuid and
    large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'
;

Would the above query improve time of execution ?

Basically what I want to know is would it make a difference to specify range and match both or the range does not matter when the match is specified.

Also, as @danblack mentioned in the comment, I would like to add one more question.

I could tell you if I have indexing on small_table or large_table but I do know what impact it would have in a different case.

So, I would also like to know how would result be affected if

large_table had indexing, but small_table didn't
small_table had indexing, but large_table didn't
Both small_table and large_table had indexing
Both small_table and large_table didn't have indexing


texts: How to select data out of an Oracle collection/array?

texts:
Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an associative array:

TYPE changedData IS RECORD (id int, name varchar2(255), vendor_id int);
TYPE changedDataArray IS TABLE OF changedData INDEX BY varchar2(255);

And I've populated it and it has all the data, awesome.

So how do I get that sent back to the client resembling rows? I could do DBMS_OUTPUT but that doesn't help when I'm trying to run this by a program expecting a tabular response. I've looked all over the internet and I'm at a loss.

Is making a temporary table and inserting the data into that and then selecting the only method? I'd do that except I don't have access to create a temporary table on the server, and getting permissions here is pulling teeth so if I can avoid that, it'd be awesome. I mean, I have the array right there - changedDataArray. And yet there seems to be no way to actually output that in a tabular fashion to the client?


texts: How does one export/import registered servers with passwords?

texts:
I have a pretty extensive collection of registered servers on SQL Server Management Studio 2016 on my box.  However, I got a new box and want to move the entire collection there.  So I right-clicked on Local Server Groups, and exported the entire set with user names and passwords.

When I try to import the set onto a new box, I get this error for every server:

If I try to export/import without user names and password, it all works fine.  But I do need user names and passwords.

What am I missing?

I am using the same windows login (my domain login) and exactly the same version of SSMS (13.0.15900.1).


texts: percona cluster victim thread on duplicate key updates

texts:
Recently, I've been getting this in the log and its creating chaos when not addressed. I totally understand the error, but what are the typical sources of this showing up in the cluster. I've been running previous versions without any issues and this has started popping up after upgrade to 8.x. Has anyone dealt with that? My cluster is behind roundrobin loadlabancer and I'm thinking of changing that, but in my opinion the cluster should be able to take care of this. Can this be tweaked with some configs ?
[Note] [MY-000000] [WSREP] Victim thread:
THD: 152232, mode: local, state: exec, conflict: certifying, seqno: -1
SQL: INSERT INTO session (data, id, expire) VALUES ('__flash|a:0:18;', 'dasd', 1621267611) ON DUPLICATE KEY UPDATE data=VALUES(data), expire=VALUES(expire)


texts:
I am trying to set up mirroring between two servers behind the same firewall on the same subnet.

I backed up mainserver and restored to mirrorserver.

I'm using the wizard on "mainserver" and connect to the "mirrorserver" using the sa login as both servers have the same sa login password.

The wizard creates endpoints on both servers correctly. When I start the mirroring it asks about FQDN but there is no domain so I click "Yes". It works for a while then I get the error alert below.

What is strange is that the error suggests it can't connect to itself.

Since posting this I found elsewhere suggestions that the problem is the database wasn't restored with NO RECOVERY on the mirrorserver. I tried this and now the wizard says

Cannot connect to mirrorserver

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

previously

Alter failed for Database 'mydatabase'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://mainserver:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)


texts: Can i have one foreign key to both parent and it's childs in postgresql?

texts:
I want to have a foreign key to reference table and it's children. Here's how I tried and failed:

CREATE TABLE parent (
 id serial,
 CONSTRAINT parent_pk PRIMARY KEY(id)
);

CREATE TABLE child (
  val text,
  constraint child_pk PRIMARY KEY(id)
) INHERITS(parent);

CREATE TABLE test (
  id serial,
  foreign_id bigint REFERENCES parent(id),
  constraint testpk PRIMARY KEY (id)
)

Lets say we have such data:

parent:
|id|
+--+
|1 | --public.parent oid
|2 | --public.child oid

child:
|id|val|
+--+---+
|2 |'a'| --public.child oid

what i whant to acheive is this:

test:
|id|foreign_id|
+--+----------+
|1 | 1        | --(from public.parent table)
|2 | 2        | --(from public.child table)

is there a good workaround here?


texts: New Analyst SQL Optimization - Multiple Spools

texts:
Unfortunately my ability to Query has outgrown my knowledge of SQL Optimization, so i am hoping someone would help a young analyst by looking at this atrocious execution plan and provide some wisdom as to how i could speed it up.  I've read a few threads about spooling, but they were all mostly a discussion about weather an Eager Table spool is good or bad, and the answer is always "it depends".

My execution plan looks like it's Spooling and Sorting the same #Temp Table multiple  times, and it's eating up a lot of execution cost.

My understanding of a Table Spool is that it is temporary storage to be used later, but if the data is already stored for later use, why would it spool the same data over and over again?  My query doesn't require any ordering so why would it need to sort the same #TempTable/Spool multiple times?

I'm so new to this, i can't figure out how to attach the entire execution plan.... so i attached an image of the bottom half of it...

Help me experienced analysts. You're my only hope.

A Little Context.

I currently have a transaction table that tracks all changes made to a lead in my CRM, and i am attempting to create a new table from this data to speed up reporting.

I am pulling data from this transaction table and flagging the first action, first user, and other firsts of a lead by using Row_Number().  I am then inserting every "first" into a #Temp Table, as i know i am going to utilize this data multiple times.

SELECT
ID,
Action,
ROW_NUMBER() OVER (PARTITION BY ID, Action ORDER BY DATE) AS ActionNum,
ROW_NUMBER() OVER (PARTITION BY ID, Actor  ORDER BY DATE) AS USERNUM
INTO #Temp
FROM Table
;

I am then Left joining this #Temp Table many times (10 times actually).  I have tried multiple other ways of solving this issue but using Row_Number multiple times seems like the best solution.

SELECT
*
FROM #temp T1
     LEFT JOIN #Temp T2
               ON T2.ID = T1.ID AND T2.Action = A2 AND T2.ActionNum = 1
     LEFT JOIN #Temp T3
               On T3.ID = T1.ID AND T3.Action = A3 AND T3.ActionNum = 1
     LEFT JOIN #Temp T4
               ON T4.ID = T1.ID AND T4.UserNum = 1
WHERE
T1.Action = A1
AND
T1.ActionNum = 1

I've looked into creating a clustered index on the #TempTable, but i must not be doing it right because it didn't change anything about my execution.

Thanks in advance for all your help! Any good reading material is also greatly apprecaited!

Best,
Austin


texts:
I am running a sql script on mysql 5.7.18 ubuntu server 16.04 that does a large number of updates in one table. Each one of the 50k updates takes under a second but the total run time is very long (>20m).

I can see from PROCESSLIST that only one thread is running and is processing the updates one-by-one.

How can I use multiple threads to speed the execution ?


texts: Is there any reason to use index for very small tables (up to 1000 rows)?

texts:
During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, red/green/blue for products.

I don't put this attributes to product table because electronic components can't be fresh, and oxygen gas can't be red and tables can't have unlimited rows count...

For storing attributes I use custom small tables where 2-3 field: id for linking, name for showing in application and sometime type if attributes groups in same category.

Primary "objects" is linked to attributes through intermediate many-to-many tables.

Is any reason to create and maintain indexes for those "small dictionaries" with less than 1000 items (usually 10-40)?

My target database is Oracle but I hope that answer vendor independent...

I fill - no, but have no technical skills to justify my filling...


texts: Why use separate databases for high traffic/volume tables?

texts:
While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things.

The first one contains the main data that changes rarely.

The second one contains event logs with high traffic and volume and the last one is an archive database for old event logs.

I was wondering what the benefit of this structure might be, because the databases are running on the same instance and the database files are located on the same disk. Therefore I would not expect any performance improvements from this.

I thought, maybe I am overlooking something and somebody can point me to benefits that I did not think of.

Update:
Some good points were made regarding maintenance and security. But I am still wondering if it is possible to get a performance improvement.

A more general question would be: Can the performance of one table suffer from other large tables in the same database (due to fragmentation or for some other reason) or are those effects probably negligible.


texts:
According to the book I am reading, a statistical database is a database that permits queries that derive aggregated information but not queries that derive individual information.
At current time, is it possible to build a statistical database? And how?
Can it be created using SQL?


texts: In Sybase, what can I do to figure out why my Transaction Log is filling up?

texts:
We have a process that runs each morning to load about 40,000 rows from one server to another.  In the past few weeks, the process has been having a 'log suspend' once or twice a week.  This is preceded by a "log segment has fallen critically low" message.

I am a programmer and not a DBA, but our DBA has said the problem is on the programming end.  But since neither the process nor the number of rows has changed, I think it's something on the database side.

The DBA said he increased the size of the transaction log a few times and that's not solving the problem.

Does anyone have any thoughts where I should start?


texts: Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

texts:
I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other words, I'd like to be able to ignore umlauts.

For example:

SELECT * FROM cities WHERE cities.name ILIKE Mu%

would match

cities
id        name
1         München

Is this possible?


texts: Querying sys.master_files over a linked server

texts:
Querying sys.master_files through a linked server, I get no results.

Why? How do I work around this issue?


texts: What permissions does the SQL Agent's user need to execute Powershell scripts?

texts:
I'm trying to create a job with a Powershell step.  To recreate this:

create new job, give it a name
add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test"
set the target as the local server.
save and execute it.

When I run it, it fails with:

  Executed as user: DOM\ServiceAcct. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error.  '.  Process Exit Code -1.  The step failed.

The trouble is, that's not a line in my script.

From what I've read, it has to do with the SQLPS provider - I suspect it's executing a wrapper script first.  Which is fine, but it sounds like the service account is not permitted to run this line?

I did log into the machine as this account and Get-ExecutionPolicy -List shows:

        Scope ExecutionPolicy
        ----- ---------------
MachinePolicy    Unrestricted
   UserPolicy       Undefined
      Process       Undefined
  CurrentUser       Undefined
 LocalMachine    RemoteSigned

I can set the policy to remotesigned, but it doesn't persist - the next powershell window I open is reverted.

Anyone have any tips?  Oh, this is SQL 2012, I don't know if that matters though.

Additionally:
I found an answer to a similar question that suggests editing the registry.  However, my HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy value was already RemoteSigned.  I set it to Unrestricted and my test works, but that doesn't seem like best practice.  I'd expect a simple script to run by default.


texts: Guide to protect SQL Server against speculative execution side-channel vulnerabilities

texts:
I am going through this link.  My question is regarding Scenario 1 under recommendation as most of our SQL Servers run on physical machine

  SQL Server runs on "bare metal" (no virtual machines)

  AND no other untrusted application logic (application tier) is run on
  the same machine

  AND no untrusted SQL Server extensibility interfaces are being used
  (see below for list).

What does it mean by untrusted application logic?

Does a webserver running on the same server with internally developed site constitute untrusted application logic?

What about a third party services that monitors the server?


texts: How do you concatenate JSONB columns in a PostgreSQL query to produce a JSONB object?

texts:
I am trying to produce a specific json object output from rows of a single column of jsonb and I'm stuck.  Before I drop into PL/pgSQL, I wanted to ask the question here. I need to concatenate a column of JSONB into a single JSON object rather than an array.

Here's an example structure of the data I need to process (cte is data is a format I don't control):

with cte as (
    select   '{"apples":[{"id":25711224,"xName":"abc"},{"id":25711225,"xName":"yzx"}]}'::jsonb hdata
        union
    select   '{"oranges":[{"id":85744212,"xName":"pop"},{"id":85744213,"xName":"ziq"}]}'::jsonb hdata
)
select
    hdata
from
    cte

Which looks like:

I want to make this JSON:

{
  "oranges": [
    {
      "id": 85744212,
      "xName": "pop"
    },
    {
      "id": 85744213,
      "xName": "ziq"
    }
  ],
  "apples": [
    {
      "id": 25711224,
      "xName": "abc"
    },
    {
      "id": 25711225,
      "xName": "yzx"
    }
  ]
}

The example has 2 rows, but I could recieve any number of rows. But I know the property name of each row (apples/oranges) will be unique, so it is really a concatenation that I want:

select '{"oranges":[{"id":85744212,"xName":"pop"},{"id":85744213,"xName":"ziq"}]}'::jsonb||'{"apples":[{"id":25711224,"xName":"abc"},{"id":25711225,"xName":"yzx"}]}'::jsonb

I've tried various groupings w/ aggregate functions, which gets me close. But not quite what I need.


texts: Restore of database failing due to pending requests

texts:
I am taking a backup of a database from one of our servers and I am placing it on my laptop so that I can run it locally.

I have tried to restore the database on to my laptop and I get the following error message:

  System.Data.SqlClient.SqlError: The transaction operation cannot be
  performed because there are pending requests working on this
  transaction. (Microsoft.SqlServer.SmoExtended).

Any pointers would be appreciated!


texts: Why can't rows inserted in a CTE be updated in the same statement?

texts:
In PostgreSQL 9.5, given a simple table created with:

create table tbl (
    id serial primary key,
    val integer
);

I run SQL to INSERT a value, then UPDATE it in the same statement:

WITH newval AS (
    INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;

The result is that the UPDATE is ignored:

testdb=> select * from tbl;
┌────┬─────┐
│ id │ val │
├────┼─────┤
│  1 │   1 │
└────┴─────┘

Why is this? Is this limitation part of the SQL standard (i.e. present in other databases), or something specific to PostgreSQL that might be fixed in future? The WITH queries documentation says multiple UPDATEs are not supported, but does not mention INSERTs and UPDATEs.


texts: mongodump: fact.bson: input/output error

texts:
We are trying to export 1.5TB database into mongodb. But its fail after 100gb and giving us below error. As we notice root mount point got 100% space. We are using below command:

mongodump -u admin -p xx_admin_db -d xx --host xx.xx.xx.xx --authenticationDatabase admin

2018-06-28T12:20:30.687+0000    [........................]  xx1.fact  14679565/468546715  (3.1%)
2018-06-28T12:20:31.192+0000    [........................]  xx1.fact  14680477/468546715  (3.1%)
2018-06-28T12:20:31.195+0000    Failed: error writing to file: write dump/xx/xxx.bson: input/output error


texts: Do both the sessions need to have UPDLOCK to avoid deadlocks

texts:
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time -

The table has clustered index on ID column

----session 1 --------
begin transaction
select * from a1

update a1
set id = 22
where id = 2

----session 2 --------
begin transaction
select * from a1

update a1
set id = 22
where id = 2

Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.

Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .

For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock

so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation. Please advise.


texts: Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

texts:
Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example:

SELECT
    CASE
        WHEN 1+1 = 2 THEN'YES'
        WHEN 1+1 = 3 THEN 'NO'
        WHEN 1+1 = 2 THEN 'NO'
    END

The results is "YES" even though the last when condition should make it evaluate to "NO". It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the case.


texts: Creating an Index based on Execution Plan

texts:
I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the performance.
But as I am looking at the execution plan to see where I could possible gain more performance, I noticing this - SQL is basically having to read 5 times the number of records that are being returned here from this one particular table and Plan Explorer is telling me this is where 24% of the query cost is coming from.

But when I look at the index that is mentioned, these are the columns that are included in the index ( no columns are included ) :

So my question is - as I try to understand what exactly I am looking at here - is there anything I can do to the existing index to help performance here?
I tried to use Brent Ozar's Paste the Plan but the XML was too large for that and since the TSQL itself is about 800 lines, I have created a folder in my Google Drive that contains the .sql file, .sqlplan file and a .pesession file ( for Sentry One Plan Explorer ).
Link to files.


texts: Expiration date on a user in a specific database

texts:
Some of our developers needs access to some of the databases in production for a short period. Is there a way for me to set an expiration date on a user in a specific database?
Just to clarify I do not want the login to expire, just the user in a specific database.

USE [Database1]
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.01.01'
--Add some roles
GO
USE [Database2]
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.02.02'
--Add some roles

MSSQL 2014, 2016 & 2017


texts: Proper design of routes in Database MySQL

texts:
I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to 1.

Currently, I'm using this schema:

What I really want, is to somehow connect all necessary tables with routes and use FK, if it's possible.

It mean's whenever I add a new category item, it should create a new route in routes table.
When I delete a specific category item, it should remove a route from routes database. As simple as that, but I'm sure I'am doing very wrong here.

Can someone suggest me what DB Schema I should use to stick with it ?


texts: Nothing changed; always get connection timeout on alter table in MariaDB

texts:
I have a MariaDB 10.1.21 database running in Fedora 25. I successfully ran alter table statements on auto_incrementcolumns in the past. After updating MariaDB to 10.1.21, but without changing any settings, I now get this with any alter table, even as root:

  ERROR 2006 (HY000): MySQL server has gone away
  No connection. Trying to reconnect...
  Connection id:    2

I temporarily increased every timeout variable, even the ones that shouldn't matter.

MariaDB [valar]> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 8000     |
| deadlock_timeout_long       | 50000000 |
| deadlock_timeout_short      | 10000    |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 31536000 |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 31536000 |
+-----------------------------+----------+

What's going on?


texts: Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

texts:
I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
Pro: No null fields.
Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
Pro: Only have to use one table for CRUD operations.
Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

EDIT: A specific question I have regards the overlapping nature of the table "NetworkDevice". This table is meant to hold network information for any device with a hostname and/or IP address, whether it is a computer, switch, or router. Is the overlapping nature of this table something that could cause problems, or is it okay to implement it this way?

Thank you in advance for any input provided. Please ask if any additional information is needed.


texts: Can shrinking temporary TEMP tablespace in Oracle cause any harm?

texts:
I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size.

Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB.

I want to shrink tablespace and I am dilema whether to use option

a) to shrink each datafile individually:

    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp01.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp03.dbf' KEEP 10MB

...

or

b) to shrink entire tablespace:

    ALTER TABLESPACE TEMP SHRINK SPACE KEEP 300MB;

c) shrink without KEEP (so it gets shrunk to minimum)

   ALTER TABLESPACE TEMP SHRINK SPACE;

I am on Oracle Enterprise 12.2.

I am wondering:

Which option is the 'best' ?
Why wouldn't one just always use option C ?
Why would one want to KEEP anything in TEMP temporary tablespace at
all at shrinking (except that bare system required minimum)?
Can any harm be done with shrinking TEMP tablespace? I will run it
    on Productional database (out of working hours tho when noone is connected to the DB) and I have to be sure.

I am running on Oracle Enterprise 12.2.


texts:
I have this MySQL Structure:

CREATE TABLE Fields (
ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ForUser VARCHAR(255) NOT NULL,
ForCategory VARCHAR(100) NOT NULL,
FieldName VARCHAR(100) NOT NULL
);
CREATE TABLE Content (
ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ForUser VARCHAR(255) NOT NULL,
ForCategory VARCHAR(100) NOT NULL,
ForField VARCHAR(100) NOT NULL,
FieldContent VARCHAR(255) NOT NULL
);

Now I want to make SQL Query that list results. Query must list content from 'Fileds' and must list content from 'Content' for specific Field. That will look like this:

  FieldName > ID 1(Table:Fields)
  -Content > ID 1(Table:Content)
  -Content > ID 2(Table:Content)... FieldName > ID 2(Table:Fields)
  -Content > ID 1(Table:Content)
  -Content > ID 2(Table:Content)...


texts: In 2-node cluster using failover with Dynamic Quorum, is Witness needed?

texts:
I have a 2-node cluster on SQL Server 2016 Standard with failover clustering configured with Dynamic Quorum.

There is one thing that made me confused: do we really need a Witness in this case?

Since I've enabled Dynamic Quorum, my cluster will not go down if one of the nodes goes down.

But some guys said, for the best practice, we still need to configure a Witness. So my question is: will a Witness make any difference?


texts: sql server snapshot replication compatibility issue

texts:
I have 2 SQL Server 2008 R2 and I am trying to set up Snapshot Replication over the Internet.  I have successfully managed to get the subscriber to connect, but now as the Job executes on the PUBLISHER, I am getting the following error:

    Target Site: VoidCheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingOptions)
    Message: Column EmissaoCRV in object Prodesp_Movimento contains type Date,
    which is not supported in the target server version, SQL Server 2000.

Both databases (published and subscriber) are configured as "Compatibility Level SQL 2000".  That said, I have tried to change the Subscriber Compatibility Level to 2008, but it didn't work.

Any ideas?  Thanks in advance!


texts: How to create Select query function in the Postgresql?

texts:
I want create select function in postgres and get all data from table. I many example see and try but not perfact execute function. What perfact syntax for select query function create in postgres?

My function code.

    CREATE OR REPLACE FUNCTION dataget()
  RETURNS SETOF tbuserlogin AS
$BODY$BEGIN

RETURN QUERY  SELECT * FROM  tbuserlogin;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dataget()
  OWNER TO postgres;

This function execute and show the datatable record  but this display record not proper column wise but only one function column display.
Like this

Thanks.


texts: Mysql master-master permission replication issue

texts:
I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication with a slave for each master(totally 4 nodes). Everything is working, databases, tables are synced but NOT permissions, permissions applied to a db is not replicated from or to any server.
I dumped mysql(mysqldump -uroot -pmypass mysql > /tmp/mysql.sql) db on original server and restored it to all the servers and also given super privileges to the repli user as mentioned in the above link. But permission are not synced from or to any server. I issued GRANT ALL PRIVILEGES... on a test db and checked with SHOW GRANTS for.. but privileges are not showing on remote servers(did flush privileges on where I granted and on all).
Posting configuration may not be useful but still here it's:
Master1(192.168.1.5)
server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.1.10
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.5

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Master2(192.168.1.10)
server-id = 20
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host = 192.168.1.5
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.10

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Isn't there any way to check if permission are replicated? with show slave status, db replication is fine and the status looks similar to below on the masters:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 4045
               Relay_Log_File: relay.000008
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4045
              Relay_Log_Space: 541
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

I have tried this setup twice(once at office and once at home), but the result is same.
Am I doing anything wrong? What's the proper way to replicate along with users and their privileges? Anybody know please?
Thanks!


texts: How can I use an environment variable in a Postgres function?

texts:
Let's say I have a windows user environment variable SOME_STRING.

Is it possible to use its value in a PL/pgSQL function?

If not, do I need to use an untrusted language (like PL/pythonu) or can I use a trusted one?

I am on PG 9.2


texts:
I am taking a SQL course and am completely stumped on this question.
Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 product' as
queryInfo
The subquery from this query will be: All customers with who have ordered more than 1 product
Note: More than one Product, not Quantity > 1 So a customer who ordered 10 of the same
product is NOT what I'm looking for.
Here is the full query I used:
SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID
             FROM Customers c
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID
         GROUP BY p.ProductID
           HAVING COUNT(*) > 1
         )
     ;

Not sure what I am doing wrong, but any input is greatly appreciated. Thanks!


texts: Replace consective numbers from a string

texts:
So if I have a table with values:

Name   ID
----   ---
James  100
James  101
James  102
James  200
James  201
James  202
Alex   100
Alex   101
Alex   102
Alex   105
Alex   106
Alex   109
Alex   109
Alex   500
Alex   501
Alex   102

I want the result to look like this

Name   ID
----   ---
James  100-102, 201-202
Alex   100-102, 105-106, 109, 500-502

I know, I can transform the data in a row by using FOR XML PATH(''). but I am not sure how to replace consecutive numbers with a - from them.  Any help in directing me to right direction will be appreciated. thanks.


texts: Name already been used by an existing constraint after renaming the table and creating a new one

texts:
I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint".

This is the create table code

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );

This is the alter table code

 alter table abc rename to cba;

This is  the code to recreate the table

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );


texts: How to install Postgresql on Ubuntu from PPA with data-checksums enabled?

texts:
We are currently installing Postgresql from the PostgreSQL apt repository. Is there some way to get "apt-get install postgresql-9.6" to use the --data-checksums option when setting up the cluster?

Or do we need to install Postgresql differently?


texts: First-In and Last-Out times for Night Shift Employees


texts: Creating new Slave without copying relay-log files from existing slave

texts:
I have a already existing master slave replication setup. I want to create a new slave but I cannot ssh  to the existing slave machine due to some issue with the slave so I cannot copy the relay log files and master.info file.
I can however connect to slave mysql using workbench and can stop slave replication if i want.
I have full acess to the master instance. Is there a way that I can create new slave ?
I am using mariadb 5.5. Thanks


texts: Demystification of SQL Server optimization process

texts:
We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example QUERYTRACEON 3604, QUERYTRACEON 8615 allows us to print out MEMO structure and QUERYTRACEON 3604, QUERYTRACEON 8619 print out a list of transformation rules applied during the optimization process. That is great, however, we have several problems with trace outputs:

It seems that the MEMO structure contains only final variants of the query plan or variants that were later rewritten into the final one. Is there a way to find "unsuccessful/unpromising" query plans?
The operators in MEMO do not contain a reference to SQL parts. For example, LogOp_Get operator does not contain a reference to a specific Table.
The transformation rules do not contain a precise reference to MEMO operators, therefore, we can not be sure which operators were transformed by the transformation rule.

Let me show it on a more elaborated example. Let me have two artificial tables A and B:
WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 9173 AS int) fkb,
    CAST(id % 911 AS int) search,
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id
        FROM x ones, x tens, x hundreds, x thousands
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search,
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

Right now, I run one simple query
SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE,
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)

I get quite complex output that describes MEMO structure (you may try by yourself) having 15 groups. Here is the picture, that visualizes MEMO structure using a tree.

From the tree one may observe that there were certain rules applied before the optimizer found the final query plan. For example join commute (JoinCommute), join to hash join (JNtoHS), or Enforce sort (EnforceSort). As mentioned it is possible to print out the whole set of rewriting rules applied by the optimizer using QUERYTRACEON 3604, QUERYTRACEON 8619 options.
The problems:

We may find JNtoSM (Join to sort merge) rewriting rule in the 8619 list, however, the sort-merge operator is not in MEMO structure. I understand that the sort-merge was probably more costly, but why it is not in MEMO?
How to know whether LogOp_Get operator in MEMO references to table A or table B?
If I see rule GetToIdxScan - Get -> IdxScan in the 8619 list, how to map it to the MEMO operators?

There is a limited number of resources about this. I have read many of the Paul White blog posts about transformation rules and MEMO, however, the above questions remain unanswered. Thanks for any help.


texts: Calculating stock quantity based on change log

texts:
Imagine that you have the following table structure:

LogId | ProductId | FromPositionId | ToPositionId | Date                 | Quantity
-----------------------------------------------------------------------------------
1     | 123       | 0              | 10002        | 2018-01-01 08:10:22  | 5
2     | 123       | 0              | 10003        | 2018-01-03 15:15:10  | 9
3     | 123       | 10002          | 10004        | 2018-01-07 21:08:56  | 3
4     | 123       | 10004          | 0            | 2018-02-09 10:03:23  | 1

FromPositionId and ToPositionId are stock positions.
Some position ID:s have special meaning, for example 0. An event from or to 0 means that stock was created or removed. From 0 could be stock from a delivery and to 0 could be a shipped order.

This table currently holds around 5.5 million rows. We calculate the stock value for each product and position into a cache table on a schedule using a query that looks something like this:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0

Even though this completes in a reasonable amount of time (around 20 seconds), I feel like this is a pretty inefficient way of calculating the stock values.
We rarely do anything but INSERT:s in this table, but sometimes we go in and adjust the quantity or remove a row manually due to mistakes by the people generating these rows.

I had an idea of creating "checkpoints" in a separate table, calculating the value up to a specific point in time and using that as a start value when creating our stock quantity cache table:

ProductId | PositionId | Date                | Quantity
-------------------------------------------------------
123       | 10002      | 2018-01-07 21:08:56 | 2

The fact that we sometimes change rows poses a problem to this, in that case we must also remember to remove any checkpoint created after the log row we changed. This could be solved by not calculating the checkpoints up until now, but leave a month between now and the last checkpoint (we very very rarely make changes that far back).

The fact that we sometimes need to change rows are hard to avoid and I would like to be able to still do this, it's not shown in this structure but the log events are sometimes tied to other records in other tables, and adding another log row to get the right quantity is sometimes not possible.

The log table is, as you can imagine, growing pretty fast and the time to calculate will only increase with time.

So to my question, how would you solve this? Is there a more efficient way of calculating the current stock value? Is my idea of checkpoints a good one?

We're running SQL Server 2014 Web (12.0.5511)

Execution plan: https://www.brentozar.com/pastetheplan/?id=Bk8gyc68Q

I actually gave the wrong execution time above, 20s was the time that the complete update of the cache took. This query takes somewhere around 6-10 seconds to run (8 seconds when I created this query plan). There's also a join in this query that was not in the original question.


texts: I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?


texts: How can I reduce recursion in Couchbase?

texts:
Sometimes, you need to access a certain document (A), but you do not know the key for that document (A), as that data is stored in a different document (B). So now, if you want to fetch A, it looks like the following:

fetch B
parse B
get name of A
fetch A

Now, obviously, this doesn't look too bad, but after three or four levels of recursion, especially once you have larger documents (100 KB+), this can become a performance issue:

fetch A (10K)
parse A
fetch B (100K)
parse B
still parsing B
fetch C (1M)
waiting for C
parse C
still parsing C
and still parsing C
fetch D (1K)

How can I reduce the amout of recursion required in a scenario like this? I mean, obviously, I could rewrite the documents, but if that is not an option, what exactly can I do?

My documents are all JSON documents; does Couchbase have some sort of feature to fetch documents recursively for me?


texts:
I would like to find out what table relation would be idea for this case a Customer to Customer business transaction where one client provides a service to another for a charge. So this is purely a service C2C and no physical products involved. Examples of services include Math tutor lessons, yoga lessons, plumbing, piano lessons, karate lessons, art lessons, housekeeping etc.

Assumptions:
1. A transaction by a buying customer can contain multiple service (job) from the same seller customer.
2. A single (same) transaction from a buyer customer cannot contain multiple services (jobs) from multiple seller Customers.

What would be the idea data model for this case? What tables would I need, and what relationships between the tables would satisfy the stated case and assumptions?

Buyer 1->> transaction 1->> transaction_detail <<-1 jobs <<-1 Seller

I tried the above relationship with 1->> = one-to-many, making table transaction_detail a many-to-many table between the transaction table and job table. But this design allows for a transaction to contain multiple items from different sellers as shown below:

 +-------------+------------+--------+---------+---------------+--------------+-------------+
| Buyer_First | Buyer_Last | job_id | trans_id | JOB           | Seller_First | Seller_Last |
+-------------+------------+--------+---------+---------------+--------------+-------------+
| Peter       | griffin    |     23 |      14 | English Tutor | Wei          | Tei         |
| Peter       | griffin    |     32 |      14 | Chinese tutor | Ping         | Wang        |
+-------------+------------+--------+---------+---------------+--------------+-------------+

We can clearly see that transaction 14 contains two jobs from two different sellers, which is against our assumptions set above.

Any ideas?


texts: Stored procedure with dynamic sql. Is it isolation safe by default?

texts:
I would appreciate confirmation on the answer I found digging through T-SQL's documentation.

I have a stored procedure that references a view twice.  Once in the procedure and then a second time when executing the dynamic sql statement.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM CourseSalesView) AS Courses

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

My database has SQL Server's default isolation level of 'ReadCommited'.  If something else changes my view in-between my procedure and sp_executesql will I have an concurrency problem?  I.E.  Could a row being removed in my view by another user at the exact right time cause a mismatch between this procedure's two statements?

I am under the assumption that the 'ReadCommited' isolation level prevents this from happening and that I do not need to explicitly declare the isolation level anywhere in my Stored Procedure as it is inherited from the default setting.


texts: SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

texts:
SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of the documentation states that these old models are deprecated, but will continue to work in SQL 2016.

Has anyone been able to figure this out? Can we use the SOAP web service to upload them? If so, do you have an example script?

Thanks


texts: Is VACUUM FULL necessary on a DROP TABLE / TRUNCATE approach for a datawarehouse

texts:
We are updating a datawarehouse on a weekly basis using a series of

TRUNCATE source_table1
COPY source_table1 FROM [...]

... for data import
and:

DROP TABLE IF EXISTS my_table
CREATE TABLE my_table AS SELECT [...]

for table updates.

We end our update process with a VACUUM FULL [VERBOSE] ANALYZE, because, as the documentation suggests, VACUUM should be done when a significant share of tuples has been updated or removed.
Here, as it is a share of 100% for all tables, we reasonably thought that VACUUM should be applied.

As we see the ouptput of the verbose option, it seems that Postgresql has not much to do, as every table VACUUMed gives:

INFO:  vacuuming "public.table345"
INFO:  "table345": found 0 removable, 9831703 nonremovable row versions in 62538 pages
DETAIL : 0 dead row versions cannot be removed yet.

I guess that on the contrary ANALYZE is more than helpfull to update the internal statistics.
Most tables are 10-100m rows big.

But we were wondering if VACUUM FULL or just VACUUM were really necessary in that case?

(Or may be the whole update process (DROP / CREATE TABLE AS) is not the right way to do?)


texts: MS Access 2010 adds unwanted wildcard at end of select clause

texts:
MS Access 2010 has a vey weird behaviour for me. Whenever I create a new Query, it silently adds a wildcard (i.e. ', *') to the end of the SELECT clause.

The wildcard is visible only in the SQL and Data views, but not in the Query Structure view.

For example, I start a new query via 'Create > Query Structure', add a table, add one column, and the SQL result is:

SELECT TABLE1.COLUMN1, *
FROM TABLE1;

What can I do to prevent that unwanted trailing wildcard?


texts: SQL Server 2017 Enterprise vs Standard Edition?

texts:
I'm looking for a good resource that gives a breakdown of the feature differences between SQL Server 2017 Enterprise and Standard editions? I want to make sure there's no gotchas or I miss anything truly beneficial by going with Standard.

I've read over the licensing guide from Microsoft, but is there any other notable feature differences it doesn't cover?

What are the vaguely described feature differences like "Advanced Adaptive Query Processing", "Enterprise Data Management", and "Advanced Security", etc?


texts:
I have roughly 1.5 million records.  This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling.

I mocked up a whole new Code First project to verify the problem is independent of my implementation, and to provide this example query:

    SELECT TOP 1 Widgets.*
    FROM [WidgetSandbox].[dbo].[Widgets] Widgets
    INNER JOIN [WidgetSandbox].[dbo].[Status] Statuses ON Widgets.StatusId = Statuses.Id
    INNER JOIN [WidgetSandbox].[dbo].[Colors] Colors ON Widgets.ColorCode = Colors.ColorCode
    INNER JOIN [WidgetSandbox].[dbo].[Sizes] Sizes ON Widgets.SizeId = Sizes.Id
    WHERE Statuses.Name = 'Available'
    AND Colors.Name = 'Red'
    ORDER BY Sizes.DiameterInches

DiameterInches is an int, as a metaphor for "PriorityLevel" in my actual code.

If I comment out ORDER BY Sizes.DiameterInches, it returns immediately, but if I want to find "the smallest available red widget", then it crawls.

Is there a better way?

Execution plan XML: https://gist.github.com/RobertBaldini/57c8b61d135cc5c84c38b2da243611ad

DDL: https://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

Repo (data loader takes several minutes): https://github.com/RobertBaldini/WidgetSandbox


texts: Query Tuning and Index Tuning - SQL Server

texts:
I've been asked to improve the performance of the following query.
I can't amend any existing Indexes but I can create new ones.

The table structures can be found in this SQL Fiddle

The row sizes of the tables are ( from development - query plans and statistics etc will be provided from development)

dbo.Logs        --6620130
dbo.LogMobiles  --6620082

For reference, the table sizes in Production are

dbo.Logs        --271856036
dbo.LogMobiles  --269611865

I updated the statistics on the tables before running the query like so

UPDATE STATISTICS dbo.Logs ;
UPDATE STATISTICS dbo.LogMobiles ;
DBCC UPDATEUSAGE(db1, 'dbo.Logs');
DBCC UPDATEUSAGE(db1, 'dbo.LogMobiles');

The query is as follows

DECLARE
     @rpStartDateTime       DATETIME    = '20180101'
    ,@rpEndDateTime         DATETIME    = '20180131' ;
DECLARE
     @iStart INT = DATEDIFF(SECOND, '19700101', @rpStartDateTime)
    ,@iEnd  INT = DATEDIFF(SECOND, '19700101', @rpEndDateTime);

SELECT
      ROW_NUMBER()OVER(PARTITION BY L.NodeId ORDER BY L.NodeId, L.GPSTime ASC)
    , V.NodeId
    , V.UnitMaxSpeed
    , M.DriverNodeId1
    , M.StatusText
    , M.Speed
    , ISNULL(S.StreetMaxSpeed,200)
FROM
    #xMobiles               V
INNER JOIN
    dbo.Logs        L   ON  L.NodeId = V.NodeId
INNER JOIN
    dbo.LogMobiles  M   ON  M.LogId = L.LogId
    --Must have Drivers identifiable
INNER JOIN
    #xDrivers       DN  ON  DN.DriverNodeId = M.DriverNodeId1
LEFT JOIN
    dbo.Streets     S   ON  S.StreetId = M.StreetId
WHERE
    L.GPSTime >= @iStart AND L.GPSTime <= @iEnd
ORDER BY L.NodeId, L.GPSTime
OPTION (MAXDOP 1) ;

The LogId from dbo.LogMobiles is a FK that references LogId (PK) from dbo.Logs.

Here is the Query Plan as is with no additional indexes

The Logical reads are as follows

From the Plan, the number of Actual Rows from dbo.Logs is 230768 which matches the final output.  The number of Actual Rows from dbo.LogMobiles (from a Clustered Index Scan) is 5676246, this is 24x greater.

Time taken to execute ~ 4 seconds.

There should be a one2one relationship between dbo.Logs and dbo.LogMobiles (although other functionality do log rows to dbo.Logs) and as you can see the big difference between the number of logical reads and actual number of rows retrieved from these tables.

I then added the following Index on dbo.LogMobiles

CREATE NONCLUSTERED INDEX IX_LogId_SBR ON dbo.LogMobiles ( DriverNodeId1 ASC, LogId ASC ) INCLUDE ( Speed, StatusText, StreetId  )

This is Plan with the new Index

This reduced the number of logical reads on dbo.LogMobiles

But increased the actual number of rows returned on the Index scan to 6620082 ( 28x greater).

Time taken to execute ~ 4 seconds - same.

What should I try next?


texts: What could be the reason for disallowing a sub query in the values clause?

texts:
For example

SQL> create table dates(d date);

Table created.

SQL> insert into dates select sysdate from dual;

1 row created.

SQL> select * from dates;

D
---------
28-MAY-11

SQL> insert into dates (d) values (select sysdate fom dual);
insert into dates (d) values (select sysdate fom dual)
                              *
ERROR at line 1:
ORA-00936: missing expression

SQL>


texts: MySQL - How to replace a given value from multiple columns when using SELECT?

texts:
I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns

    A01    A02    A03    A04    A05  ... A200

  99999  99999     10    753  99999     99999

As you can see, it has about 200 columns. It uses 99999 to model a NULL or undefined value. So, in my SELECT, i need to replace those values with NULL or even a empty String. Is it possible by using a single SELECT or should i use something else ?

ATT: you could suggest UPDATE those values with NULL - and then SELECT -, but it is a legacy system which we cannot modify them


texts: Weird result when grouping and self-joining a table

texts:
I'm on MySQL 5.5.49, the results of the following query seem wrong to me.

Test data :

CREATE TABLE `test` (
  `invoice_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `previous_invoice_item_id` int(10) unsigned DEFAULT NULL,
  `price` decimal(12,4) unsigned NOT NULL,
  PRIMARY KEY (`invoice_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test (invoice_id, previous_invoice_item_id, price)
VALUES (1, NULL, 1), (1, 1, 0);

Query :

SELECT
 ii.invoice_item_id,
 pii.invoice_item_id,
 ii.previous_invoice_item_id,
 ii.price - pii.price,
 ii.price,
 pii.price
FROM test ii
JOIN test pii ON pii.invoice_item_id = ii.previous_invoice_item_id
GROUP BY ii.invoice_item_id;

The problem is that ii.price - pii.price returns 0 when it should return -1. If I remove the GROUP BY clause, the result is correct. If the difference is positive, the result is also correct.

I'm at a loss to explain the result, can someone enlighten me?


texts: MySql Event to Delete Data from 2 Tables Older Than 2 Months

texts:
Im new to MySql events and I never used them before so please to bear with me. With My DB I have order_details and invoices tables which I need to create an Event to delete all the records older than 2 months and where order_details payment_status = 3 and invoices status = 2 and I want the event to make the deleting every day at 3AM How I may accomplish this Any help will be much appreciated
Both tables have timestamp column named created_at
Tables Definition
A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

I tried below command but Im getting errors,
CREATE EVENT IF NOT EXISTS daily_delete ON SCHEDULE EVERY 1 DAY STARTS 2020-09-13 04:00:00 DO BEGIN DELETE FROM bariq_DB.invoice where timestampdiff(MONTH,created_at,NOW()) > 2 and status = 2; delete from bariq_DB.order_details where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; END

Errors :
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1


texts: How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

texts:
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some performance issues.

What is a good way to determine if there are uncommitted transactions (bonus points if there is a way to know if the connection they're on is idle or not)?

Thanks very much!


texts:
I currently have a impdp job running for a fairly large schema (2TB) and ran into an error.  The source database has one column that is larger then the destination database.  This is causing ORA-02374 and ORA-12899 errors.  This has caused the import job to come to a crawl on this one table.

I am trying to get past the errors, and the best solutions I can come up with are to either fix the table definition, or tell the import job to skip the current table.  I can not currently fix the table definition because the data pump job has the table locked.

Is there a way to pause the job, make the column modification, and then resume the import?  Alternatively is there a way to tell the job to skip the current table, and move on?  This would allow to to come back once the job is finished, fix the table definition, and then re-import just the one table.

ETA:  This is one of the last few tables in the import, I would rather not kill the whole thing and start over.


texts: Query to determine a count by rolling interval

texts:
I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of our messageing.  Messageing is triggered based on orders in this case.  The idea is that a customer will reciev only one message regardless of how many orders placed untill the interval rolls over. for example

John orders on Monday,  message goes out.  (count = 1)
John orders on Thurday,
John orders on Sunday,
John orders on Tuesday  message goes out.  (count = 2)

To further complicate things there isnt a static set of intervals.  The interval begins for the customer at the time of their first order.  Thus Johns interval starts with Monday but Susans interval may start with Friday.

This is what I've got so far.  Any help would be appreciated.

DECLARE @Interval INT = 7

SELECT o.intappid,
       o.intCustomerID,
       MAX(o.intOrderID),
       MAX(o.dtshipdate),
       DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
  FROM .dbo.[order] o
 WHERE YEAR(o.dtShipDate) = 2012
   AND o.intAppID = 18
   AND o.intCustomerID = 296
 GROUP BY o.intCustomerID, o.intAppID, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
 order by o.intAppID, o.intcustomerid, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval

sample output from this query

intappid    intCustomerID   (No column name)    (No column name)    (No column name)
18          296             21954               2012-03-07          9
18          296             22671               2012-04-25          16
18          296             22728               2012-04-30          17
18          296             23354               2012-06-14          23


texts:
I have a question about SQL Server databases.

I have initialized a database (let's say Configuration_DB) of size 120GB following with certain application installation. This application installation has taken up 20GB out of that 120GB DB space and 100 GB is left free.

Now if I run shrink job on that, will it be reduced to 20Gb (plus something) or how much it can be reduced to, although the Configuration_DB.mdf file has already taken 120 GB on disk.

Thanks.